计算MySQL中的时间差，但要考虑事件

Question

我有一个这样的表结构

index - date ------ time ----- status
1       2015-01-01  13:00:00   start
2       2015-01-01  13:10:00   continue
3       2015-01-01  13:20:00   continue
4       2015-01-01  13:30:00   end
5       2015-01-01  13:30:00   ready
6       2015-01-01  13:40:00   start
7       2015-01-01  13:50:00   continue
8       2015-01-01  15:00:00   end

我想要做的是计算开始和结束之间的时间(即索引1-4是30分钟，6-8是20分钟)，但只考虑了第一个开始和第一个结束，所以查询不会选择索引1-8的时间差。哪个查询用于计算两个状态( start-end )之间的时间差，并显示多个start-end实例的结果，而不会将它们批处理到一个事件中？

Answer 1

对于每次开始，使用查询来获取下一个结束时间。然后，只需计算差值。逻辑是这样的：

select t.*, timestampdiff(second, dt, next_dt)
from (select t.*, addtime(t.date, t.time) as dt,
             (select addtime(t2.date, t2.time)
              from table t2
              where addtime(t2.date, t2.time) > addtime(t.date, t.time) and
                    status = 'end'
              order by addtime(t2.date, t2.time) desc
              limit 1
             ) as next_dt
      from table t
      where status = 'start'
     ) t

这里假设您的date和time列是使用适当的数据库类型(date和time)存储的。如果您将它们存储为其他格式，则必须不必要地将逻辑复杂化以将它们转换为内部格式。

Answer 2

请考虑以下几点：

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(id  INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,dt DATETIME NOT NULL
,status VARCHAR(12) NOT NULL
);

INSERT INTO my_table VALUES
(1       ,'2015-01-01 13:00:00' ,  'start'),
(2       ,'2015-01-01 13:10:00' ,  'continue'),
(3       ,'2015-01-01 13:20:00' ,  'continue'),
(4       ,'2015-01-01 13:30:00' ,  'end'),
(5       ,'2015-01-01 13:30:00' ,  'ready'),
(6       ,'2015-01-01 13:40:00' ,  'start'),
(7       ,'2015-01-01 13:50:00' ,  'continue'),
(8       ,'2015-01-01 15:00:00' ,  'end');

SELECT x.*
     , TIMEDIFF(MIN(y.dt),x.dt)diff 
  FROM my_table x 
  JOIN my_table y 
    ON y.dt >= x.dt 
 WHERE x.status = 'start' 
   AND y.status = 'end' 
 GROUP 
    BY x.id;
+----+---------------------+--------+----------+
| id | dt                  | status | diff     |
+----+---------------------+--------+----------+
|  1 | 2015-01-01 13:00:00 | start  | 00:30:00 |
|  6 | 2015-01-01 13:40:00 | start  | 01:20:00 |
+----+---------------------+--------+----------+