时间戳

首页 > 解决方案 > 根据条件计算事件的总时间

mysql - 根据条件计算事件的总时间

问题描述

我有一张下表

CREATE TABLE log (  
     id INT NOT NULL AUTO_INCREMENT,  
     keyNo VARCHAR(16),  
     date DATE NOT NULL,
     time TIME NOT NULL,
     t1Event VARCHAR(255),
     t2Event VARCHAR(255),  
     PRIMARY KEY  (id)  
)

我有以下方式的表格行

1 205 2018-07-10 12:01 Lclosed keyout
2 205 2018-07-10 12:02 Lclosed keyout
3 205 2018-07-10 12:03 Lclosed keyinside
4 205 2018-07-10 12:44 Lclosed keyout
5 205 2018-07-10 13:02 Lclosed keyinside

执行以下查询后得到的这张表

Select * from reportsuser where keyNo = '205' and date = '2018-07-10' and t1Event='Lclosed'

不，我想计算密钥出来的总时间，可以通过第一个keyout事件和第一个事件keyinside之后的第一个来计算keyout。像这样我想通过总结所有这样的时间差来计算总时间，例如在上面我想要(3 - 1) + (5 - 4)的情况下sno。我怎样才能实现这个mysql查询？由于此类查询的时间段可能是月或年，因此最好的方法是什么？

标签： mysql

解决方案

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

INSERT INTO log VALUES
(1, '2018-07-10 12:01:00' ,'keyout'),
(2, '2018-07-10 12:02:00' ,'keyout'),
(3, '2018-07-10 12:03:00' ,'keyinside'),
(4, '2018-07-10 12:44:00' ,'keyout'),
(5, '2018-07-10 13:02:00' ,'keyinside');


SELECT MIN(dt) dto 
     , dti
     , SEC_TO_TIME(TIME_TO_SEC(dti)-TIME_TO_SEC(MIN(dt))) diff
  FROM 
     (  SELECT x.dt
             , MIN(y.dt) dti
          FROM log x
          JOIN log y ON y.dt > x.dt 
           AND y.status = 'keyinside'
         WHERE x.status = 'keyout'
         GROUP 
            BY x.dt
     ) a
 GROUP
    BY dti;

    +---------------------+---------------------+----------+
    | dto                 | dti                 | diff     |
    +---------------------+---------------------+----------+
    | 2018-07-10 12:01:00 | 2018-07-10 12:03:00 | 00:02:00 |
    | 2018-07-10 12:44:00 | 2018-07-10 13:02:00 | 00:18:00 |
    +---------------------+---------------------+----------+

推荐阅读