mysql - 使用 MySQL TIMEDIFF 进行时间计算

Question

我有下表:

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 | 
+----+---------------------+---------------------+---------------------+---------------------+

我想将总工作时间和“所需工作时间”的差相加。它与下面的语句配合得很好，但由于未知原因它不适用于 id 6。start*/stop* 字段的格式为 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;

这是结果:

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     | 
+----+-----------+

预先感谢您的每一个提示。

Answer 1

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 总时间 FROM 次 按编号分组