I have table that holds records with tasks, status and time when triggered:
Table tblwork:
+-------------+------------+---------------------+-----+
| task | status | stime | id |
+-------------+------------+---------------------+-----+
| A | 1 | 2018-03-07 20:00:00 | 1 |
| A | 2 | 2018-03-07 20:30:00 | 2 |
| A | 1 | 2018-03-07 21:00:00 | 3 |
| A | 3 | 2018-03-07 21:30:00 | 4 |
| B | 1 | 2018-03-07 22:30:00 | 5 |
| B | 3 | 2018-03-07 23:30:00 | 6 |
+-------------+------------+---------------------+-----+
Status 1 means start, 2 - pause, 3 - end
Then I need to calculate how much time is spent for each task excluding pause (status = 2). This is how I do it:
SELECT t1.id, t1.task,
SUM(timestampdiff(second,IFNULL(
(SELECT MAX(t2.stime) FROM tblwork t2 WHERE t2.task='B' AND t2.stime< t1.stime) ,t1.stime),t1.stime)) myTimeDiffSeconds
FROM tblwork t1
WHERE t1.task='B' and (t1.status = 1 or t1.status = 3);
Now I want to get table for all tasks
SELECT t1.id, t1.task,
SUM(timestampdiff(second,IFNULL(
(SELECT MAX(t2.stime) FROM tblwork t2 WHERE t2.stime< t1.stime) ,t1.stime),t1.stime)) myTimeDiffSeconds
FROM tblwork t1
WHERE (t1.status = 1 or t1.status = 3) GROUP BY t1.taks
I get this result:
+-------------+------------+---------------------+
| task | id | mytimedifference |
+-------------+------------+---------------------+
| A | 1 | 3600 |
| B | 3 | 2421217 |
+-------------+------------+---------------------+
Calculation for A is correct B is wrong, it should be 3600 second but i don't understand why.