Converting Time Units With Math

* To convert seconds to hours, simply divide by 3600 (since each hour has 60 seconds * 60 minutes). The remainder is the remaining seconds.
* To convert seconds to minutes, simply divide by 60. The remainder is the remaining seconds.

Nothing too shocking there, right? So, let's do some math. If we have a TotalSeconds, we can get:

* Hours = (TotalSeconds / 3600)
* Remaining Minutes = (TotalSeconds % 3600) / 60
* Remaining Seconds = (TotalSeconds % 60)

(The % is the modulo operator in T-SQL, which returns the remainder when dividing two integers.)

Thus, we can write our SQL like this to return 3 integer columns (Hours, Minutes, Seconds) for each event:

select
EventID,
TotalSeconds / 3600 as Hours,
(TotalSeconds % 3600) / 60 as Minutes,
TotalSeconds % 60 as Seconds
from
(
select EventID, DateDiff(second, StartDate, EndDate) as TotalSeconds
from Events
) x


Results

EventID Hours Minutes Seconds
----------- ----------- ----------- -----------
1 6 11 22
2 7 42 29
3 0 21 6
4 3 51 21
5 1 15 53
6 5 24 0
7 16 50 2


Calculating Duration Totals

select
sum(TotalSeconds / 3600) as Hours,
sum((TotalSeconds % 3600) / 60) as Minutes,
sum(TotalSeconds % 60) as Seconds
from
(
select EventID, DateDiff(second, StartDate, EndDate) as TotalSeconds
from Events
) x

Hours Minutes Seconds
----------- ----------- -----------
38 214 133


select
sum(TotalSeconds) / 86400 as Days,
(sum(TotalSeconds) % 86400) / 3600 as Hours,
(sum(TotalSeconds) % 3600) / 60 as Minutes,
sum(TotalSeconds) % 60 as Seconds
from
(
select EventID, DateDiff(second, StartDate, EndDate) as TotalSeconds
from Events
) x

Days Hours Minutes Seconds
----------- ----------- ----------- -----------
1 17 36 13