Sum Time

Question

Sum Time

lanky_doodle

SSCrazy

Points: 2378
More actions
May 28, 2013 at 3:00 am

#274216

Hey,
I have a column named TOTALTIME (datetime data type). This is used to store the amount of time something takes rather than a clock time.
So a user presses Start on a timer and then presses Stop 10 minutes later, the value would be 00:10:00. 2 hours 10 minutes between pressing Start and Stop would be 02:10:00 etc. etc.
Not my application by the way!
I need to sum up these times, so based on my 2 examples the total would be 02:20:00.
Seconds are NOT used, just hh:mm.
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

HanShi SSC-Dedicated Points: 33506 More actions · Answer 1

Just add both values. The return type will also be a datetime value which you can CONVERT to the desired format. If you want to calculate just the minutes, you'll need to convert the final value to string, split into seperate days, hours and minutes and add them all up.

declare @time1 datetime

declare @time2 datetime

set @time1 = '02:10:00'

set @time2 = '00:11:00'

select

@time1 + @time2-- sum of datetime values

, CONVERT(varchar(8), @time1 + @time2, 114)-- converted to string in time format

, substring(CONVERT(varchar(8), @time1 + @time2, 114),1, 2) * 60-- hours converted to minutes

, substring(CONVERT(varchar(8), @time1 + @time2, 114),4, 2)-- minutes

, substring(CONVERT(varchar(8), @time1 + @time2, 114),1, 2) * 60 + substring(CONVERT(varchar(8), @time1 + @time2, 114),4, 2)-- total minutes

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

HanShi SSC-Dedicated Points: 33506 More actions · Answer 2

You can also define a "zero" time (set @time = '0:00:00') and use this with the DATEDIFF to get the passed time

datediff(minute, @time, @time1 + @time2)

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

Ed Wagner SSC Guru Points: 287024 More actions · Answer 3

The datetime data type is meant to store a point in time, not a duration of time. If you ever have to store a duration greater than 24 hours, you're going to have to get into using the date portion of the value, but you won't know which rows this applies to, so you're going to start running into trouble. If you absolutely know you'll never have to store a duration greater than 24 hours, it'll work.

If you have the opportunity to do some redesign, I would consider changing the data type into a numeric one to allow for longer durations. Your specific business requirements may trump this and it might not be needed at all, but it's just something to consider for the future.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

lanky_doodle SSCrazy Points: 2378 More actions · Answer 4

Thanks guys.

That's given me the starting point I need.

Jeff Moden SSC Guru Points: 1003872 More actions · Answer 5

lanky_doodle (5/29/2013)
Thanks guys.
That's given me the starting point I need.

You have to be a little bit careful when it come to performance on such things, meaning that you should severely limit the number of conversions to character based data in the process.

You've asked for the result in the HH:MM format. My only question is what do you want to do for results 24 or more hours? Do you want a format of dddddd:hh:mm or a format of hhhhhhhh:mm? Truth be told, I prefer decimal hours for such a thing because of how easy the conversion is but that may not fit your requirements.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)