can TIME values be aggregated?

[new DBA in '09]

Hello,

Can time values be aggregated? I'm putting together a summary report
that will need to show the sum time that something happened.
(Employee breaks, for instance.) If break times for 3 employees were
"01:00:00", "00:15:00", and "00:12:30" in length, how do I aggregate
them to get "01:27:30"? Simply treating them like char values so they
can be parsed into integers and added and then divided by 60 doesn't
seem like the best way.

Thanks,
Eric

[Bob Barrows]

What is the datatype of the column you are storing these in? If these
are durations, then you should be storing them as integers (number of
seconds) so they can easily be aggregated. Storing them in the manner
you have chosen has forced you to take an extra step: if you are storing
them in a datetime (or char) column, then you need to convert them into
the number of seconds since midnight in order to aggregate them. Use
DateDiff to calculate the seconds since midnight before aggregating
them. Putting them into h:m:s format is, well, a formatting issue for
whatever application is displaying the result.

--
HTH,
Bob Barrows

[Tom]

You cannot aggregate on a TIME datatype. TIME defines a time of day
not a length of time. You can aggregate on the DIFF between start and
end times.

[Erland Sommarskog]

SELECT datediff(ss, '00:00', length_of_break)

gives you the length of the breaks in seconds.

Then, let's see, this might work:

SELECT convert(char(8),
dateadd(ss, SUM(datediff(ss, '00:00', length_of_break)),
convert(time, '00:00'),
108)

But I did not test.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[new DBA in '09]

At first read, your answers seemed logical and simple to enact, but
I'm having a hard time with the rendering now.

Since my employees are located world-wide, a starting break time could
conceivably be 11:50:00 p.m. from to 12:50:00 a.m. That's 3600
seconds. So far so good, since I'm using "DATEDIFF(second, StartDate,
EndDate)" to figure out the number of seconds in the timespan. When
they all sum to a value greater than 86,400, however, the value that
gets returned is always less than 24 hours, leading me to believe a
TIME value cannot exceed :23:59:59.999. OK, so I understand why you
suggest keeping integer values in the database and formatting them in
the app.

Any idea how I can format 293842134 seconds as a time value in SSRS
2008? One that has the potential to exceed 24 hours, such as
"26:19:34" (signifying 26 hours, 19 minutes, 34 seconds)?

Thanks, Bob and Tom. Five stars for both posts.

[Tom Cooper]

I know I told you to do things like this in the front end. But I'm not an
SSRS person, so I can't help you with how to do it there. If you can't find
a way, it can be done in SQL, the following SQL will tell you that
2938442134 seconds is 81,622 hours, 48 minutes and 54 seconds.

Declare @Test Table(StartTime datetime, EndTime datetime);
Insert @Test(StartTime, EndTime)
Values (Current_Timestamp, DateAdd(second, 293842134, Current_Timestamp));

With cte As
(Select StartTime, EndTime, DATEDIFF(second, StartTime, EndTime) As Seconds
From @Test)
Select StartTime, EndTime, Seconds As DiffInSeconds,
CAST(Seconds / 3600 As varchar(11)) + ':'
+ CAST((Seconds / 60) % 60 As varchar(2)) + ':'
+ CAST((Seconds % 60) % 60 As varchar(2)) As 'DiffInH:M:S'
From cte;

Tom

"new DBA in '09" <ericb...@gmail.com> wrote in message
news:5e58f430-55b7-472e...@i31g2000yqm.googlegroups.com...