Time calculations with MySQL TIMEDIFF

Question

Hi there,

I have the following table:

mysql> SELECT id,start1,stop1,start2,stop2 FROM times;
+----+---------------------+---------------------+---------------------+---------------------+
| id | start1              | stop1               | start2              | stop2               |
+----+---------------------+---------------------+---------------------+---------------------+
|  4 | 2010-04-23 08:05:00 | 2010-04-23 12:15:00 | 2010-04-23 12:45:00 | 2010-04-23 16:50:00 | 
|  2 | 2010-04-26 09:30:00 | 2010-04-26 12:10:00 | 2010-04-26 12:50:00 | 2010-04-26 16:50:00 | 
|  7 | 2010-04-28 08:45:00 | 2010-04-28 11:45:00 | 2010-04-28 13:10:00 | 2010-04-28 17:29:00 | 
|  6 | 2010-04-27 09:30:00 | 2010-04-27 12:15:00 | 2010-04-27 12:55:00 | 2010-04-27 18:44:00 | 
+----+---------------------+---------------------+---------------------+---------------------+

I want to sum total worktime and the difference to the "needed work hours". It works pretty well with the statement below, but for unknown reasons it doesn't work for id 6. start*/stop* fields are in format datetime.

SELECT *, TIME_FORMAT(TIMEDIFF(totaltime,'08:24'),'%H:%i') AS diff,
totaltime > '08:24' AS redorgreen FROM
(
SELECT 
    DATE_FORMAT(start1,'%a %e. %M %Y') AS date,
    TIME_FORMAT(SUM(TIMEDIFF(stop1,start1) + TIMEDIFF(stop2,start2)),'%H:%i') AS totaltime,
    TIME_FORMAT(start1,'%H:%i') AS start1,
    TIME_FORMAT(stop1,'%H:%i') AS stop1,
    TIME_FORMAT(start2,'%H:%i') AS start2,
    TIME_FORMAT(stop2,'%H:%i') AS stop2,
    id as id
FROM times GROUP BY id ASC
) AS somethingwedontneed;

This is the result:

select id,
       TIME_FORMAT(SUM(TIMEDIFF(stop1,start1) + TIMEDIFF(stop2,start2)),'%H:%i') 
       AS totaltime from times group by id;
+----+-----------+
| id | totaltime |
+----+-----------+
|  2 | 06:40     | 
|  4 | 08:15     | 
|  6 | NULL      | 
|  7 | 07:19     | 
+----+-----------+

Thanks in advance for every hint.

Answer 1

Hi check this,

SELECT id, TIMEDIFF( stop1, start1 ) , TIMEDIFF( stop2, start2 ) , ADDTIME( TIMEDIFF( stop1, start1 ) , TIMEDIFF( stop2, start2 ) ) , TIME_FORMAT( ADDTIME( TIMEDIFF( stop1, start1 ) , TIMEDIFF( stop2, start2 ) ) , '%H:%i' ) AS totaltime
FROM times
GROUP BY id