SUM() of time column using MySQL

Question

I'm trying to create a query to sum time grouped by user and the result should be in minutes.

contents of table:

duration user

00:06:00 user1

00:06:00 user2

00:04:00 user3

00:01:00 user55

00:02:00 user1

00:01:00 user2

00:01:00 user55

Result expected:

duration user

8 user1

7 user2

4 user3

2 user55

This is the closer I can get:

SELECT user, SEC_TO_TIME( SUM( TIME_TO_SEC(

duration

) ) ) AS totaltime from salientes group by user

However this outputs the result as 000:00:00 and for some cases, this ends on 838:59:59.

Any help will be much appreciated.

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

    mysql> SELECT MINUTE(SEC_TO_TIME(SUM(TIME_TO_SEC( duration)))) 
  as tt ,user from tt group by user;
    +------+--------+
    | tt   | user   |
    +------+--------+
    |    8 | user1  |
    |    7 | user2  |
    |    4 | user3  |
    |    2 | user55 |
    +------+--------+
    4 rows in set (0.00 sec)

SUM() of time column using MySQL

Sign up