Sum multiple times produced in a query (timediff)

Question

I have two fields:

• Initial (timestamp)
• Final (timestamp)

My query is:

SELECT TIMEDIFF(Final, Initial) 
    AS 'Worked Hours' 
    FROM `db_foo`
    WHERE matriculation='X' AND date='2017-yy-yy'

The result will be something like

Worked Hours    
03:34:00
02:34:00
01:00:00
[...]

Would it be possible to further sum those multiple timestamps, so to have the TOTAL amount of worked hours?

Example dataset (exported in csv):

DATE --- ID --- INITIAL --- FINAL --- MATRICULATION

2017-09-14,"29","2017-09-14 11:00:00","2017-09-14 14:34:00","4"
2017-09-14,"30","2017-09-14 17:00:00","2017-09-14 19:34:00","4"
2017-09-14,"31","2017-09-14 21:00:00","2017-09-14 22:00:00","4"

Desired output (it is the sum of the working times):

Worked Hours
07:08:00

Thanks in advance

Answer 1

To get the desired result you can use following query

SELECT SEC_TO_TIME(
  SUM(
    TIMESTAMPDIFF(SECOND,Initial,Final)
  )
)
FROM `db_foo` /* WHERE clause*/; 

To get the total sum along with previous result set you can follow below approach

SELECT t.*,SEC_TO_TIME(SUM(workedhours))
FROM (
  SELECT ID, TIMESTAMPDIFF(SECOND,Initial,Final)  workedhours
  FROM `db_foo`  /* WHERE clause*/
 ) t
GROUP BY ID WITH ROLLUP;

DEMO

Answer 2

Try this one

used SEC_TO_TIME and TIME_TO_SEC .

SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(Final, Initial))))
AS 'Worked Hours' 
FROM `db_foo`
WHERE matriculation='X' AND date='2017-yy-yy'

I hope it will help you.

Answer 3

The TIME type has a maximum value of 838:59:59. If you think the sum might exceed 838 hours, it's not safe to sum TIME expressions. I would recommend instead converting the time differences to minutes and displaying the total number of hours as a decimal number, rather than a time:

SELECT
  ROUND(SUM(TIMESTAMPDIFF(MINUTE, Initial, Final) / 60.0), 1) AS "Worked Hours"
FROM `db_foo`
WHERE matriculation='X' AND date='2017-yy-yy';

which would return

Worked Hours
7.1