# Performing SUM over a column of datatype TIME

Few days back one of my colleague asked me a very interesting problem of adding time. He wanted to know how to add time in a similar fashion just add we add integers using SUM() function.

This can be done very easily: Let us first create a table time_store to store TIME format [hh:mm:ss.msc] and insert some values in this table.

```
CREATE TABLE time_store
(
tme TIME
)
GO

--Following code loops 5 times
--and inserts time in time_store table with a delay of 5 seconds

WAITFOR DELAY '000:00:5'
INSERT INTO time_store VALUES (CONVERT(TIME,GETDATE()))
GO 5

SELECT * FROM time_store
GO

tme
----------------
02:09:05.8370000
02:09:10.8500000
02:09:15.8670000
02:09:20.8800000
02:09:25.8930000
```

Adding time is a simple three step process:
1) Convert your time to seconds
2) Add all the seconds together
3) Convert your seconds back in time format [hh:mm:ss.msc]

Below code snippet gives you the result.

```
SELECT CONVERT(TIME, DATEADD(s, SUM(( DATEPART(hh, tme) * 3600 ) + ( DATEPART(mi, tme) * 60 ) + DATEPART(ss, tme)), 0)) AS total_time
FROM   time_store

total_time
----------------
10:46:15.0000000
```

To understand, let us investigate the code step by step

1) Convert time into seconds

Formula: Hours x 60(mins) x 60(secs) + Minutes x 60 (secs) + Seconds

```
SELECT tme,
( DATEPART(hh, tme) * 3600 ) +
( DATEPART(mi, tme) * 60 ) +
DATEPART(ss, tme) AS tme_in_sec
FROM   time_store

tme              tme_in_sec
---------------- -----------
02:09:05.8370000 7745
02:09:10.8500000 7750
02:09:15.8670000 7755
02:09:20.8800000 7760
02:09:25.8930000 7765
```

2) Use the simple SUM() formula now to add seconds

```
SELECT SUM(
( DATEPART(hh, tme) * 3600 ) +
( DATEPART(mi, tme) * 60 ) +
DATEPART(ss, tme)
)    AS total_sec
FROM   time_store

total_sec
-----------
38775
```

3) Convert total seconds to time format [hh:mm:ss]

Simple tip to do this is to use ADDDATE() function. Using this function you should add total seconds to a dummy date. This will return you with a datetime value. Clip-off the date part by converting datetime to time using CONVERT() function.

Let us firt generate a dummy datetime, this can be done with:

```
SELECT DATEADD(s, 0, 0) AS dummy_datetime

dummy_datetime
-----------------------
1900-01-01 00:00:00.000
```

Here we are adding zero seconds to dummy date. If we add total seconds we calculated earlier to this dummy date, it will automatically convert seconds to time format.

```
DECLARE @total_sec INT    -- declared variable to hold total seconds

SELECT @total_sec  =  SUM(( DATEPART(hh, tme) * 3600 ) +
( DATEPART(mi, tme) * 60 ) +
DATEPART(ss, tme))
FROM   time_store

SELECT DATEADD(s, @total_sec, 0) AS total_datetime
FROM   time_store

total_datetime
-----------------------
1900-01-01 10:46:15.000
```

Observe the date and time part, date part matches with that of dummy date but total seconds are converted into time format. Now to get rid of the date part, convert this datetime to time format using CONVERT() function.

```
SELECT CONVERT(TIME, DATEADD(s, @total_sec, 0)) AS total_time
FROM   time_store

total_time
----------------
10:46:15.0000000
```

If you think this is the end, then you are wrong, there is a catch here. Let us try this logic on a different set of data and check if it works fine or not.

New data set being the following, let us apply our code:

```
tme
----------------
13:06:55.8030000
13:07:00.8300000
13:07:05.8400000
13:07:10.8500000
13:07:15.8600000

DECLARE @total_sec INT

SELECT @total_sec  =  SUM(( DATEPART(hh, tme) * 3600 ) +
( DATEPART(mi, tme) * 60 ) +
DATEPART(ss, tme))
FROM   time_store

SELECT CONVERT(TIME, DATEADD(s, @total_sec, 0)) AS total_time
FROM   time_store

total_time
----------------
17:35:25.0000000
```

Do you think this is correct?
Absolute No No!!

Let us remove the CONVERT() function from the above code and check the result in datetime format

```
SELECT DATEADD(s, @total_sec, 0) AS total_datetime
FROM   time_store

total_datetime
-----------------------
1900-01-03 17:35:25.000
```

Clearly observe the date part, dummy date has got incremented by two days. With a small modification in the code, we can actually calcuate total time in days, hours:mins:secs

```
SELECT @total_sec / ( 24 * 3600 ) as total_days, convert(time,DATEADD(s, @total_sec, 0)) AS total_datetime

total_days  total_datetime
----------- ----------------
2           17:35:25.0000000
```

If we apply the same code to the data set chosen earlier, it gives the following result.

```
total_days  total_datetime
----------- ----------------
0           10:46:15.0000000
```

Hope you all enjoyed reading this post.

Happy Learning
Lokesh Vij

## 10 thoughts on “Performing SUM over a column of datatype TIME”

1. Great one.. I am sure it’ll help lot of developers..

Like

2. Thanks Brij.
Regards, Lokesh

Like

3. Very nice. I didn’t catch the # of days issue initially. Simpler way to do the intial conversion to # of seconds btw:

```DECLARE @time AS TIME = GETDATE()
SELECT DATEDIFF(s,'00:00:00',@time)```

Like

1. Thanks for your appreciation and trick to convert time in seconds. Much appreciated!

Like

4. Prabir

How to handle this if the hour increase 24 hour. That means my seconds is 15464395. How to get this as hh:mm:ss format????

Like

1. Hi Prabir,

I am not able to get your question. Kindly elaborate.

Thanks,
Lokesh

Like

5. Saif

I also want name with this

SELECT CONVERT(TIME, DATEADD(s, SUM(( DATEPART(hh, tme) * 3600 ) + ( DATEPART(mi, tme) * 60 ) + DATEPART(ss, tme)), 0)) AS total_time
FROM time_store

that display like

Ali 00:20:45
Aslam 00:54:34

Like

1. Hi Saif,

Here you go:

SELECT ‘Atif ‘
+ CAST( (CONVERT(TIME, DATEADD(s, SUM(( DATEPART(hh, tme) * 3600 ) + ( DATEPART(mi, tme) * 60 ) + DATEPART(ss, tme)), 0))) AS VARCHAR(8)) AS total_time
FROM time_store

The idea is to convert the time format to character so as to concatenate with character string.

Hope this is what you were looking for.

Thanks
Lokesh

Like

6. Thank you so much, after searching 2 3 hours finally i got result..thanks again good work..

Like