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