susbtracting time and counting minutes elapsed

[cesaru]

Hello,

I'm trying to get a total of how many minutes an assigment too to be completed

userid carlosh
Assignment 12345
start_dtim 2010-05-23 16:02:17
end_dtim 2010-05-23 16:27:53

userid emilyc
Assignment 12346
start_dtim 2010-05-23 16:13:17
end_dtim 2010-05-23 16:29:55


userid emilyc
Assignment 12347
start_dtim 2010-05-23 16:30:17
end_dtim 2010-05-23 16:35:55

So basically I need to look like

carlosh 1 assigment 24.72 Minutes
emilyc 2 assigment 21.00 Minutes

I tried doing something simple likes
select usert_id,count(Assignment),sum( end_dtim-start_dtim)

which works but the results for time I get is something like :0 00:25:36
or if the assignments goes over couple of hours it will be like 0 02:25:36 or even it addes days like
3 01:45:36 (3 days one hours and 45 minutes)


How can I convert the results in minutes.

I've been pulling my hair for this. I appreciate any help.

thanks,

 

[olded]

I had a similar problem only I needed the number of seconds between 2 datetimes. I wrote a stored procedure that returns the number of seconds. You will probably have to modify the interval. The declaration would be probably be something like:

# UNTESTED
DEFINE int_sec INTERVAL MINUTE TO MINUTE;

-- This function returns the number of seconds between datetime dt1 and dt2.
-- change to character first because the interval to integer conversion fails.  
CREATE PROCEDURE xint_in_sec(dt1 DATETIME YEAR TO SECOND, dt2 DATETIME YEAR
TO SECOND) RETURNING INTEGER;
   DEFINE int_sec INTERVAL SECOND(9) TO SECOND;
   DEFINE xchar CHAR(20);
   DEFINE xint INTEGER;

   LET int_sec = dt2 - dt1;  -- get the interval
   LET xchar = int_sec; -- change to char
   LET xint = xchar; -- change to integer

RETURN xint;
END PROCEDURE;

 

[cesaru]

thanks.. I think I got it.

(a.end_dtim-a.start_dtim)::interval minute(9) to minute::char(10)::int) As elapsed_time

hope it helps someone.