计算 MySQL 中的时差但考虑到事件 [英] Calculating time difference in MySQL but taking events into account

问题描述

我有一个这样结构的表格

I have a table structured like this

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 的时间差.哪个查询用于计算两个雕像(开始 - 结束)之间的时间差并显示多个开始 - 结束实例的结果，而不会将它们批处理为一个事件?

and what I would like to do is count the time between start and end (ie. index 1-4 is 30min, 6-8 is 20min), but taking into account only the first start and first end, so that the query doesn't choose the time difference of index 1-8. Which query is used to calculate time difference between two statues (start-end) and show the result for multiple instances of start-end without them getting batched into one event?

推荐答案

考虑以下...

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

这篇关于计算 MySQL 中的时差但考虑到事件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！