Solved

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

Posted on 2015-02-19
9
88 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
 
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
[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

 

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

863 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

Need Help in Real-Time?

Connect with top rated Experts

27 Experts available now in Live!

Get 1:1 Help Now