Solved

how to sum total hours from date-time field in sql

Posted on 2015-02-19
9
94 Views
Last Modified: 2015-02-20
I am trying to sum total hours that someone was sleeping from date-time field.  For example,  if a person sleeps at 1:00 am and wakes up on 5:00 am then i want to show that person slept for 4 hours as total hours of sleep from 1 to 5 am.  I want to extract the hour and the minutes from the date-field.  Here is how it look like my date-field
2015-02-19 00:00:00.000
2015-02-19 00:30:00.000
2015-02-19 01:00:00.000
2015-02-19 01:30:00.000
2015-02-19 02:00:00.000
2015-02-19 02:30:00.000
2015-02-19 03:00:00.000

Open in new window

This is from 12:00 AM to 3:00 AM so i would expect the total to be 3 Hours.  
I tried this but not working..
select  datepart(hour, cast(FullDatetime as time)) as totHours
from myTable
where id in (1, 2, 3,4,5, 7)

Open in new window

0
Comment
Question by:moe57
  • 5
  • 4
9 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40619686
Really not following this, but if you're asking for a way to calculate the difference in times between the min and max of a given set, then copy-paste the below into your SSMS, execute to verify it works, then modify to meet your needs
CREATE TABLE #tmp (tm datetime) 

INSERT INTO #tmp (tm) 
VALUES 
('2015-02-19 00:00:00.000'), 
('2015-02-19 00:30:00.000'), 
('2015-02-19 01:00:00.000'), 
('2015-02-19 01:30:00.000'), 
('2015-02-19 02:00:00.000'), 
('2015-02-19 02:30:00.000'), 
('2015-02-19 03:00:00.000')

SELECT DATEDIFF(minute, Min(tm), Max(tm))
FROM #tmp
-- WHERE <whatever>

Open in new window

0
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 40619690
To return hours, with the decimal places to make sure 2.75 doesn't get cut off to 2 ...
SELECT DATEDIFF(minute, Min(tm), Max(tm)) / 60.0 as hours
FROM #tmp

Open in new window

0
 

Author Comment

by:moe57
ID: 40619716
Jim,  i have not looked at  your example yet but all what i need is somehow to sum total hours from the example i mentioned above.  The field has date and time so all what i need is to add up all the times and get the total number of hours which in this case is 3 hours.  I hope i am little bit clearer now.  thanks
0
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
LVL 65

Expert Comment

by:Jim Horn
ID: 40619727
Define for us at a Barney level of simplicity how the above set of datetime values adds up to three hours.
0
 

Author Comment

by:moe57
ID: 40619776
okay, i am happy to do that..
('2015-02-19 00:00:00.000'),  = 12:00 AM
('2015-02-19 00:30:00.000'),  = 12:30 AM
('2015-02-19 01:00:00.000'),  = 1:00 AM
('2015-02-19 01:30:00.000'),  = 1:30 AM
('2015-02-19 02:00:00.000'),  = 2:00 AM
('2015-02-19 02:30:00.000'),  = 2:30 AM
('2015-02-19 03:00:00.000')  =  3:00 AM
So when you add up from 12:00 am to 3:00 Am  total hours = 3 hours.  I am using military hours.  Thanks
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40619780
That was provided to you in my first comment, where it takes the difference between the minimum and maximum values, then does a DATEDIFF to convert to minutes.  The second comments converts to hours with a decimal value.

If that's not what you need, explain again how the calculation is supposed to work.
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40621300
I think where we may be tripping up here is the use of 'sum up', which implies summing all rows, i.e. midnight + 12:30 + 1 + 1:30 + 2 + 2:30 + 3 would equal 10:30.

I interpreted your question as the maximum value minus the minimum value.

If your definition of 'sum up' is different from the above two, spell it out in more clear language.
0
 

Author Comment

by:moe57
ID: 40622177
Jim, thanks for your help.  You did answer my question i just did not see your second answer where you were dividing 60.  Thanks
0
 

Author Closing Comment

by:moe57
ID: 40622179
thanks
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how the fundamental information of how to create a table.

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question