MySql：计算时间间隔

Question

MySql：计算时间间隔

3

假设我有一个记录警报系统的数据库。每隔一定时间，会插入一个新的行。这一行包括警报ID、日期时间以及警报是否开启的标志。

我想知道在一段时间内（今天、本周、本月），警报设置为开启（armed=1）的时间有多长。这将是后续具有armed=1的行。如果有一个带有armed=0的行，则不能计算它。

例如：

id      alarm_id        armed       date        
1       1               0           2012-01-01 00:00:00
2       1               1           2012-01-01 00:10:00
3       1               1           2012-01-01 00:20:10
4       1               1           2012-01-01 00:29:58
5       1               0           2012-01-01 00:40:00
6       1               1           2012-01-01 01:00:00
7       1               1           2012-01-01 01:10:00
8       1               1           2012-01-01 01:20:00
9       1               0           2012-01-01 01:30:00

在这个例子中，警报从00:10:00到00:29:58被布防=> 19:58分钟。并且从01:00:00到01:20:00被布防=> 20分钟。对于这一天，警报至少已经被布防了39:58分钟，这必须是我的查询结果。

日志就像一个心跳，所以警报可以在“开启”和“关闭”状态之间被布防，但我只想知道后续行是否已经布防=1。

我一直在苦思冥想。我已经将表连接到自己上，但我就是想不出该如何做。这真的可能吗？我已经设置了一个SQL Fiddle来处理数据：http://sqlfiddle.com/#!2/9eca2/2/0 谢谢。

- user1540127

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Omesh · Accepted Answer

您需要使用类似以下的MySQL查询语句来实现此操作：

SELECT armed_at INTO @v FROM alarm ORDER BY armed_at LIMIT 1;

SELECT armed, TIMEDIFF(armed_at, @v), @v:=armed_at FROM alarm;

更多细节请尝试：

SET @t =0;
SELECT armed_at INTO @v FROM alarm ORDER BY armed_at LIMIT 1;
SELECT armed, 
       IF(armed = 1, @t:=ADDTIME(TIME(TIMEDIFF(armed_at, IF(@v <> 0, @v, armed_at))), @t) , @t:=0) AS time, 
       IF(armed = 1, @v:=armed_at, @v := 0) AS v 
FROM alarm;

更新查询将为您提供确切的结果，尝试使用：

SET @a=0, @d=0, @t=0;

SELECT atime
FROM (
      SELECT id, armed,
             IF((@a = 1 AND armed = 1), @t:=ADDTIME(TIME(TIMEDIFF(adate, IF(@d <> 0, @d, adate))), @t) , 0) AS atime,
             IF((@a:=armed) = 1, @d:=adate, @d := 0) AS d
      FROM alarmlog
     ) a
WHERE a.armed = 1
ORDER BY id DESC
LIMIT  1;