MySql: Counting intervals

Question

Suppose I have a database with a log of alarm systems. With an interval, a new row in the database is inserted. This row has the alarm_id, a datetime and a flag for whether the alarm is on or off.

I would like to know for how long an alarm is set to on (armed=1) in a period of time (today, this week, this month). That would be the subsequent rows that have an armed=1. If there is a row with an armed=0, it may not be counted.

For example:

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

In this example, the alarm was armed from 00:10:00 to 00:29:58 => 19:58 minutes. And armed from 1:00:00 to 1:20:00 => 20 minutes. For this day the alarm has been armed for at least 39:58 minutes and that has to be the outcome of my query.

The log is like a heartbeat so the alarm can be armed between the "on" and "off" state, but I only want to know what subsequent rows are armed=1.

I have been breaking my head about this. I joined the table on itself, but I just can't figure out how to do this. Is it even possible? I have set op an SQL Fiddle to play with the data: http://sqlfiddle.com/#!2/9eca2/2/0

Thanks.

Answer 1

You will have to use something like this to do this in MySQL using single query:

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

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

For more details try:

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;

Updated query will give you the exact results which you want, try:

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;