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

Advertisements

8 thoughts on “Performing SUM over a column of datatype TIME

  1. 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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s