MySQL: How to SUM() a TIMEDIFF() on a group?

Question

So I've got a set of results that looks something like this:

SELECT User_ID, StartTime, EndTime, TIMEDIFF(EndTime, StartTime) AS TimeDiff
FROM MyTable

------------------------------------------------------------------
| User_ID |       StartTime     |         EndTime     | TimeDiff |
------------------------------------------------------------------
|    1    | 2010-11-05 08:00:00 | 2010-11-05 09:00:00 | 01:00:00 |
------------------------------------------------------------------
|    1    | 2010-11-05 09:00:00 | 2010-11-05 10:00:00 | 01:00:00 |
------------------------------------------------------------------
|    2    | 2010-11-05 06:30:00 | 2010-11-05 07:00:00 | 00:30:00 |
------------------------------------------------------------------
|    2    | 2010-11-05 07:00:00 | 2010-11-05 09:00:00 | 02:00:00 |
------------------------------------------------------------------
|    2    | 2010-11-05 09:00:00 | 2010-11-05 10:00:00 | 01:00:00 |
------------------------------------------------------------------

Now I need to group the results by User_ID and SUM() TimeDiff. If I add a GROUP BY clause, it doesn't SUM() the TimeDiff (and I wouldn't expect it to). How can I SUM() the TimeDiffs for each User?

Answer 1

Use:

  SELECT t.user_id,       
         SEC_TO_TIME(SUM(TIME_TO_SEC(t.endtime) - TIME_TO_SEC(t.starttime))) AS timediff
    FROM MYTABLE t
GROUP BY t.user_id

Steps:

1. Use TIME_TO_SEC to convert TIME to seconds for math operation
2. Sum the difference
3. Use SEC_TO_TIME to convert the seconds back to TIME

Based on the sample data, I'd have just suggested:

  SELECT t.user_id,       
         TIMEDIFF(MIN(t.startdate), MAX(t.enddate)) AS timediff
    FROM MYTABLE t
GROUP BY t.user_id   

NOTE: There is a bug in this code if you are using datetime. TIME_TO_SEC only converts the time section so you end up with big negatives if the clock goes past midnight. Use UNIX_TIMESTAMP instead to do the sum. Also SEC_TO_TIME maxes out at values greater than 3020399 seconds e.g. SELECT TIME_TO_SEC(SEC_TO_TIME(3020400)); If you see this value 838:59:59 you've reached the max and probably just need to divide by 3600 to just show hours.

Answer 2

AFAIK, your only option is to cast to UNIX_TIMESTAMPs and do some integer calculations, substituting a random date (I chose 2000-01-01) for TIME columns without a date.

SELECT TIMEDIFF(
    DATE_ADD('2000-01-01 00:00:00',
       INTERVAL 
       SUM(UNIX_TIMESTAMP(CONCAT('2000-01-01 ',TimeDiff)) - UNIX_TIMESTAMP('2000-01-01 00:00:00')
       SECOND),
    '2000-01-01 00:00:00')
FROM MyTable;

Because it may seem you can SUM TIME columns, but actually they will be cast to nasty integers or floats which will not follow time specifications (try it with a sum of minutes > 60 and you'll see what I mean).

---

For the ones who claim you can SUM time columns:

mysql> create table timetest(a TIME);
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO timetest VALUES ('02:00'),('03:00');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT SUM(a) FROM timetest;
+--------+
| SUM(a) |
+--------+
|  50000 |
+--------+
1 row in set (0.00 sec)

mysql> SELECT TIME(SUM(a)) FROM timetest;
+--------------+
| TIME(SUM(a)) |
+--------------+
| 05:00:00     |
+--------------+
1 row in set (0.00 sec)

mysql> -- seems ok, but wait
mysql> INSERT INTO timetest VALUES ('02:30');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT TIME(SUM(a)) FROM timetest;
+--------------+
| TIME(SUM(a)) |
+--------------+
| 07:30:00     |
+--------------+
1 row in set (0.00 sec)

mysql> -- and now, oh ye unbelievers:
mysql> INSERT INTO timetest VALUES ('01:40');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT TIME(SUM(a)) FROM timetest;
+--------------+
| TIME(SUM(a)) |
+--------------+
| NULL         |
+--------------+
1 row in set, 1 warning (0.00 sec)

mysql> -- why is that? because it uses integer arithmetic, not time - arithmetic:
mysql> SELECT SUM(a) FROM timetest;
+--------+
| SUM(a) |
+--------+
|  87000 |
+--------+
1 row in set (0.00 sec)

mysql> -- that cannot be cast to time

Answer 3

I'll suggest you use TO_SECONDS instead:

SELECT t.user_id,       
         SEC_TO_TIME(SUM(TO_SECONDS(t.endtime) - TO_SECONDS(t.starttime))) AS timediff
    FROM MYTABLE t
GROUP BY t.user_id

Answer 4

If you are experiencing this bug, a good alternative is to use TIMESTAMPDIFF and then get the sum of the column you create.

select(sum(df.elapse)/60 as diff
from( SELECT c1.start, c1.end, TIMESTAMPDIFF(MINUTE,c1.start,c1.end) as elapse
from c1) df

Note of caution: You will get very close to the actual number of hours but may be a few decimal points off. See floating point rounding for more details.

Answer 5

This worked for me in a time tracking app. In short, I converted all intervals into seconds, added them up, and converted back to TIME format. I am a beginner, so please excuse any clumsiness in the code. I always welcome feedback.

CREATE table time_segments (segment_id int not null auto_increment primary key, sign_in DATETIME, sign_out DATETIME, Seconds INT not null, Display TIME not null, user_id INT);

UPDATE time_segments SET Seconds=TIME_TO_SEC(-TIMEDIFF(sign_in,sign_out));

UPDATE time_segments SET Display=SEC_TO_TIME(Seconds);

INSERT INTO time_segments (sign_in, sign_out, user_id) VALUES ('2019-03-12 14:01:00', '2019-03-12 16:45:00', 1), ... ;

mysql> select * from time_segments;

| segment_id | sign_in | sign_out | Seconds | Display |

| 1 | 2019-03-12 14:01:00 | 2019-03-12 16:45:00 | 9840 | 02:44:00 |

mysql> SELECT SEC_TO_TIME(SUM(Seconds)) AS 'Payperiod Hours' FROM time_segments;

| Payperiod Hours | +-----------------+ | 49:29:00 |

Answer 6

Will it work for your?

SELECT User_ID, TIME(SUM(TIMEDIFF(EndTime, StartTime))) AS TimeDiff
FROM MyTable GROUP BY User_ID