计算同一表上各行之间的日期时间行之间的差异 [英] Calculating difference on datetime row betwen rows on the same table

问题描述

我有一个表，其中包含触发时任务，状态和时间的记录:

I have table that holds records with tasks, status and time when triggered:

表格表:

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

状态1表示开始，2-暂停，3-结束

Status 1 means start, 2 - pause, 3 - end

然后，我需要计算除暂停(状态= 2)外，每个任务花费了多少时间.这是我的方法:

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

我得到这个结果:

+-------------+------------+---------------------+
| task        | id         | mytimedifference    |
+-------------+------------+---------------------+
| A           | 1          | 3600                |   
| B           | 3          | 2421217             |
+-------------+------------+---------------------+

计算A是正确的B是错误的，应该是3600秒，但我不明白为什么.

Calculation for A is correct B is wrong, it should be 3600 second but i don't understand why.

推荐答案

假设每次暂停和结束总是有一个起点，那么这样会更直接吗?

Assuming there is always a start for each pause and end, wouldn't something like this be more direct?

SELECT t.task
   , SUM(TO_SECONDS(t.stime) 
         * CASE WHEN t.status IN (1) THEN -1
                WHEN t.status IN (2, 3) THEN 1
                ELSE 0
           END
     ) AS totalTimeSecs
FROM tblwork AS task
GROUP BY t.task

我不太确定TO_SECONDS()产生的值对于当前时间戳有多大；但是如果将它们加在一起是一个问题，可以将其更改为

I'm not quite sure offhand how big the values that come out of TO_SECONDS() are for current timestamps; but if they are an issue when being summed, if could be changed to

   , SUM((TO_SECONDS(t.stime) - some_constant_just_before_or_at_your_earliest_seconds)
         * CASE WHEN t.status IN (1) THEN -1
                WHEN t.status IN (2, 3) THEN 1
                ELSE 0
           END
     ) AS totalTimeSecs

---

您可以通过将以下内容添加到选择表达式列表中来检测异常"数据

---

You can detect "abnormal" data by adding the following to the select expression list

, CASE WHEN SUM(CASE 
                WHEN t.status IN (1) THEN -1 
                WHEN t.status IN (2, 3) THEN 1 
                ELSE 0 END
              ) = 0 
       THEN 'OK' 
       ELSE 'ABNORMAL' 
   END AS integrityCheck

注意:任何未封闭"的间隔都将被标记为异常；如果没有更复杂，更昂贵的开始和结束检查间隔来区分打开"和无效"，那么可能是最好的选择. 用于附加"integrityCheck"的总和等于-1可能暗示一个开放的时间间隔，但也可能指示错误的双开始.

Note: any "unclosed" intervals will be marked as abnormal; without much more complicated and expensive start and end checking for intervals to differentiate "open" from "invalid", it's probably the best that can be done. The sum used for additonal "integrityCheck" equaling -1 might hint at an open ended interval, but could also indicate an erroneous double-start.

这篇关于计算同一表上各行之间的日期时间行之间的差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！