Time calculations with MySQL TIMEDIFF
- by Oli
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.
