×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

susbtracting time and counting minutes elapsed

susbtracting time and counting minutes elapsed

susbtracting time and counting minutes elapsed

(OP)
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,



 

RE: susbtracting time and counting minutes elapsed

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;

CODE

-- 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;
 

RE: susbtracting time and counting minutes elapsed

(OP)
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.
 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close