开始和结束时间

ymzxtsji 于 2021-06-18 发布在 Mysql

关注(0)|答案(2)|浏览(576)

我有一张table，上面有设备和它们的状态。每5分钟将每个设备的状态保存到数据库中。数据库中的每条记录都包含设备名称（、def等）、设备状态（1=正常、6=停机和11=计划停机）、将记录添加到数据库的时间和添加记录的日期。
现在我想知道每台设备状态改变时的开始和结束时间。
我使用的是MySQL5.6版本。

CREATE TABLE IF NOT EXISTS `demo` (
    `Id` INT(11) NOT NULL AUTO_INCREMENT,
    `Name` VARCHAR(255) NOT NULL,
    `StateN` INT(11) NOT NULL,
    `TimeAdded` INT(11) NOT NULL,
    `DateAdded` INT(11) NOT NULL,
    PRIMARY KEY (`Id`)
);

INSERT INTO `demo` (`Id`, `Name`, `StateN`, `TimeAdded`, `DateAdded`) VALUES
  (1, 'ABC', 1, 1540250401, 1540245600),
  (2, 'DEF', 1, 1540250401, 1540245600),
  (3, 'GHI', 1, 1540250401, 1540245600),
  (4, 'JKL', 11,1540250401, 1540245600),

  (5, 'ABC', 1, 1540250701, 1540245600),
  (6, 'DEF', 1, 1540250701, 1540245600),
  (7, 'GHI', 1, 1540250701, 1540245600),
  (8, 'JKL', 11, 1540250701, 1540245600),

  (9, 'ABC', 1, 1540251001, 1540245600),
  (10, 'DEF', 1, 1540251001, 1540245600),
  (11, 'GHI', 6, 1540251001, 1540245600),
  (12, 'JKL', 11, 1540251001, 1540245600),

  (13, 'ABC', 1, 1540251301, 1540245600),
  (14, 'DEF', 1, 1540251301, 1540245600),
  (15, 'GHI', 6, 1540251301, 1540245600),
  (16, 'JKL', 11, 1540251301, 1540245600),

  (17, 'ABC', 1, 1540251601, 1540245600),
  (18, 'DEF', 1, 1540251601, 1540245600),
  (19, 'GHI', 1, 1540251601, 1540245600),
  (20, 'JKL', 11,1540251601, 1540245600);

我想得到以下输出：

Name StateN StartTime  EndTime
ABC  1      1540250401 1540251601
DEF  1      1540250401 1540251601
GHI  1      1540250401 1540250701
GHI  6      1540251001 1540251301
GHI  1      1540251601 1540251601
JKL  11     1540250401 1540251601

mysql unix-timestamp

来源：https://stackoverflow.com/questions/53602066/getting-start-and-end-time-when-status-changed-in-mysql

2条答案

按热度按时间

vof42yt11#

这会返回一些类似于您所追求的东西，这让我怀疑您的结果集中有错误。。。

SELECT name
     , staten
     , MIN(timeadded) starttime
     , MAX(timeadded) endtime
  FROM 
     ( SELECT a.* 
         FROM 
            ( SELECT name
                   , staten
                   , timeadded
                   , CASE WHEN @prev_name = name 
                          THEN CASE WHEN @prev_staten = staten 
                                    THEN @i:=@i 
                                    ELSE @i:=@i+1 END 
                          ELSE @i:=@i+1 END i
                   , @prev_name := name prev_name
                   , @prev_staten:=staten prev_state
                FROM demo
               ORDER 
                  BY name
                   , timeadded
                   , staten
            ) a
         JOIN
            ( SELECT @prev_name := null, @prev_staten := null, @i:=0) vars
     ) x
 GROUP
    BY name
     , staten
     , i
 ORDER
    BY name
     , starttime
     , staten;

+------+--------+------------+------------+
| name | staten | starttime  | endtime    |
+------+--------+------------+------------+
| ABC  |      1 | 1540250401 | 1540251601 |
| DEF  |      1 | 1540250401 | 1540251601 |
| GHI  |      1 | 1540250401 | 1540250701 |
| GHI  |      6 | 1540251001 | 1540251301 |
| GHI  |      1 | 1540251601 | 1540251601 |
| JKL  |     11 | 1540250401 | 1540251601 |
+------+--------+------------+------------+

赞(0）回复(0）举报 2021-06-18

6rqinv9w2#

更新：这是不正确的答案，因为我错过了“当状态改变”的一部分在操作。

SELECT
      Name
    , StateN
    , MIN(TimeAdded)
    , MAX(TimeAdded)
FROM
    demo
GROUP BY
    Name, StateN
;

赞(0）回复(0）举报 2021-06-18

我来回答

开始和结束时间

2条答案

相关问题

热门标签

最新问答