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

我有下表：

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

谢谢你的每一个暗示。

最佳答案

选择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'）作为totaltime
从古至今
按id分组

关于mysql - 使用MySQL TIMEDIFF进行时间计算，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/2735628/

TimeDiff

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