Solved

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

Posted on 2015-02-19
9
95 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
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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 setup several different housekeeping processes for a SQL Server.

791 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