时间戳

首页 > 解决方案 > MySQL状态变化之间的时间差

mysql - MySQL状态变化之间的时间差

问题描述

我有一张桌子，上面有灯及其相应的状态：

timestamp           name    state
2019-03-07 11:16:32 Light_A 0
2019-03-07 12:36:32 Light_A 1
2019-03-07 13:15:12 Light_A 0
2019-03-08 02:11:45 Light_A 1
2019-03-08 02:18:45 Light_A 1
2019-03-08 02:22:45 Light_A 0
2019-03-18 03:14:45 Light_B 0
2019-03-18 03:16:45 Light_B 1
2019-03-18 03:18:45 Light_B 1
2019-03-18 03:20:45 Light_B 0
2019-03-19 17:20:12 Light_B 0
2019-03-19 17:22:12 Light_B 1
2019-03-19 17:23:12 Light_B 0

我想测量每个灯打开了多少小时。

到目前为止，我得到了：

SELECT x.*, TIMEDIFF(MIN(y.timestamp),x.timestamp)diff 
FROM data x 
  JOIN data y 
    ON y.timestamp >= x.timestamp 
WHERE x.state = 1 AND y.state = 0
GROUP 
    BY x.timestamp;

除非两个连续的行具有相同的 State = 1，否则效果很好

上面的命令返回：

timestamp           name    state   diff
2019-03-07 12:36:32 Light_A 1       00:38:40
2019-03-08 02:11:45 Light_A 1       00:11:00
2019-03-08 02:18:45 Light_A 1       00:04:00
2019-03-18 03:16:45 Light_B 1       00:04:00
2019-03-18 03:18:45 Light_B 1       00:02:00
2019-03-19 17:22:12 Light_B 1       00:01:00

相反，我希望得到这样的结果：

timestamp           name    state diff
2019-03-07 12:36:32 Light_A 1     00:38:40
2019-03-08 02:11:45 Light_A 1     00:11:00
2019-03-18 03:16:45 Light_B 1     00:04:00
2019-03-19 17:22:12 Light_B 1     00:01:00

请你帮助我好吗？

提前谢谢了。

标签： mysql

解决方案

首先您必须消除所有对结果没有贡献的行，例如一行与state = 1之后的一行，state = 1然后获取该行state = 0以计算差异：

select 
  t.timestamp,
  t.name, 
  t.state
  timediff(coalesce(
    (select timestamp from tablename where state = 0 and timestamp = 
      (select min(timestamp) from tablename where timestamp > t.timestamp and state = 0)), now()), 
  t.timestamp) diff
from tablename t
where 
  t.state = 1 and coalesce((select state from tablename where timestamp = 
    (select max(timestamp) from tablename where timestamp < t.timestamp)), 0) = 0

查看演示
结果：

| timestamp           | name    | state | diff     |
| ------------------- | ------- | ----- | -------- |
| 2019-03-07 12:36:32 | Light_A | 1     | 00:38:40 |
| 2019-03-08 02:11:45 | Light_A | 1     | 00:11:00 |
| 2019-03-18 03:16:45 | Light_B | 1     | 00:04:00 |
| 2019-03-19 17:22:12 | Light_B | 1     | 00:01:00 |

推荐阅读