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
wdelaney05Asked:
Who is Participating?
 
Naveen KumarConnect With a Mentor Production Manager / Application Support ManagerCommented:
if there are no gaps in the times for all the records, then try this :

select (max(seg_end) - min(seg_start) ) * 24 from your_table
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
the output given by the query will be in hours.
0
 
sdstuberCommented:
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
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
sdstuberConnect With a Mentor Commented:
if your values are dates, you can skip the "to_date" portion and simply sum the difference * 24
0
 
wdelaney05Author Commented:
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))

0
 
wdelaney05Author Commented:
and yup, sdstuber, my values are dates.
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
Good and thanks.
0
 
sdstuberCommented:
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
0
 
wdelaney05Author Commented:
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!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.