1

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

flapane
  • 543
  • 2
  • 8
  • 21

3 Answers3

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
markusk
  • 6,477
  • 34
  • 39
  • Also i see no difference in your approach which is same as mine except you are calculating difference in minutes – M Khalid Junaid Sep 14 '17 at 08:49
  • The difference in my approach is that it works if the total number of hours exceeds 838. – markusk Sep 14 '17 at 10:23
  • Thank you, your approach is definitely smart. The table will be refreshed at midnight, so that the value will not exceed 24 hours. – flapane Sep 14 '17 at 11:33
2

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

M Khalid Junaid
  • 63,861
  • 10
  • 90
  • 118
  • If you're going to sum times, why not `SUM(TIMEDIFF(Initial, Final))` instead of `SEC_TO_TIME(SUM(TIMESTAMPDIFF(SECOND,Initial,Final))`? Also, the `TIME` data has a maximum value of `838:59:59`. This query will overflow if the hours worked exceed 838 hours. – markusk Sep 14 '17 at 08:35
  • @markusk apologies but i am unbale to get your point first query sums the seconds returned by TIMESTAMPDIFF and then its formatting its now as per user's wish if OP want to get the difference returned in hours,minutes or seconds etc SEC_TO_TIME is just for showing as formatted duration – M Khalid Junaid Sep 14 '17 at 08:48
  • My point is that `SEC_TO_TIME` converts the difference from `INTEGER` (seconds) to `TIME`, and `TIME` is constrained to a max value of `838:59:59`, which means you will get wrong results if the total number of hours exceed 838. As an example, `SELECT TIMESTAMPDIFF(SECOND, '2016-01-01', '2017-01-01') / (60 * 60)` returns 8784 (which is correct), while `SELECT SEC_TO_TIME(TIMESTAMPDIFF(SECOND, '2016-01-01', '2017-01-01'))` returns the wrong result `838:59:59`. – markusk Sep 14 '17 at 10:22
  • 1
    Thank you both, now I can solve my issue and feed the output via AJAX. – flapane Sep 14 '17 at 11:34
0

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.

Vasanth
  • 443
  • 7
  • 16