asked on

Summation of datetime fields

In SQL, how would I sum these values together to get total hours? The total should be about 18.09 hours.

Sample data:
SEG_START        SEG_END
2011.01.05 00:40:00      2011.01.05 04:50:00
2011.01.05 04:50:00      2011.01.05 07:00:00
2011.01.05 07:00:00      2011.01.05 08:00:00
2011.01.05 08:00:00      2011.01.05 09:00:00
2011.01.05 09:00:00      2011.01.05 10:00:00
2011.01.05 10:00:00      2011.01.05 11:00:00
2011.01.05 11:00:00      2011.01.05 12:00:00
2011.01.05 12:00:00      2011.01.05 13:00:00
2011.01.05 13:00:00      2011.01.05 14:00:00
2011.01.05 14:00:00      2011.01.06 18:45:00

ASKER CERTIFIED SOLUTION

Naveen Kumar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Naveen Kumar

the output given by the query will be in hours.

Sean Stuber

assuming your values are strings then try this...

select sum(24 * (to_date(seg_end,'yyyy.mm.dd hh24:mi:ss') - to_date(seg_start,'yyyy.mm.dd hh24:mi:ss'))) from yourtable

note, your total is 42.0833333333 hours because your last row is 28.75 hours

if you change the date to 2011.01.05 then it will come out to 18.0833333333

SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

W D

ASKER

both of you are correct, thanks! I just took the time to test out all comments.
I tried select (max(seg_end) - min(seg_start) ) * 24
and I also tried SUM(((seg_end - seg_start) * 24))

W D

ASKER

and yup, sdstuber, my values are dates.

Naveen Kumar

Good and thanks.

Sean Stuber

note the max-min method is only valid if the start/end are contiguous

if there are any holes then you'll need to sum the individual pieces

W D

ASKER

sdtuber, re: note the max-min method is only valid if the start/end are contiguous
if there are any holes then you'll need to sum the individual pieces

Noted, thanks. My data will be contiguous, thank goodness. I have other tables with data that won't be contigous, so I'll have to keep that in mind.

Thanks again to both of you!