Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2015-02-19
9
Medium Priority
?
125 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 66

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 66

Accepted Solution

by:
Jim Horn earned 2000 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 66

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 66

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 66

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
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.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

604 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