使用MySQL TIMEDIFF进行时间计算 [英] Time calculations with 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 | 
+----+---------------------+---------------------+---------------------+---------------------+

我想将总工作时间与所需工作时间"的差额相加.它可以很好地与以下语句配合使用，但是由于未知原因，它不适用于id6.start */stop *字段的格式为datetime.

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;

这是结果:

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

预先感谢每一个提示.

推荐答案

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总时间 从时代 按ID分组

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

这篇关于使用MySQL TIMEDIFF进行时间计算的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！