MySQL query for check alerts during a timelapse and condition

Question

Hello I have next table:

EventID     datetime                        value
1           2012-12-15 12:45:06             15.3
2           2012-12-15 13:00:06             16.9
3           2012-12-15 13:15:06             17.3
4           2012-12-15 13:30:06             16.9
5           2012-12-15 13:45:06             18.8
6           2012-12-15 14:00:06             21.3
7           2012-12-15 14:15:06             21.9
9           2012-12-15 14:30:06             22.7
10          2012-12-15 14:45:06             23.2
11          2012-12-15 15:00:06             23.6

And the conditions are:

value>=15 and value<=20 and this condition must be for a one HOUR or more continuous

for this set of data, query must show (count):

alert, corresponding to events ID 1, 2, 3, 4, 5

I try with:

select * from events where value>=15 and value<=20

but I dont know how I detect a continuous range of one Hour.

Thanks in advance.

You cannot reliably do this from SQL. Scrape the relevant results in a programming language like PHP or C# and detect the contiguousness there. — Niels Keurentjes, Jan 14 '14 at 20:41
That's a complicated and quite expensive query. Have you considered using a trigger? — Tony Hopkinson, Jan 14 '14 at 20:47
@TonyHopkinson Yes, but the same problem, I have to remember first time when the condition starts and check if next 15 minutes this is into the interval. — ManuParra, Jan 14 '14 at 20:50
The table ty triggered an insert to would do remember for you. It's not trivial, but you run a query to get this sort of results while you are collecting data, you could end up with a number of issues. — Tony Hopkinson, Jan 14 '14 at 21:04

score 0 · Answer 1 · answered Jan 14 '14 at 21:28

A solution using triggers

Assuming you are inserting in the events table and always at the current and only one process is doing it.

If you create a table called say transitions

TransitionID StartTime, EndTime

Then in a insert trigger on the events table

If the value >= 15 and <= 20
  If the Transitions table is empty or the last record has an endtime 
  create a new record with the startTime of the event

If the value < 15 or > 20
  if the Transitions table is not empty and the last record has no endtime
    if an hour has passed update the endtime 
    if it hasn't delete the last transition record

peterm · Accepted Answer · 2014-01-15T00:14:12.517

Try it this way

SELECT COUNT(*) total
  FROM
(
  SELECT 1
    FROM
  (
    SELECT *, 
           @n := @n + 1 rnum, 
           @g := IF(value BETWEEN 15 AND 20, @g + 1, 1) grnum, 
           @p := value
      FROM table1 CROSS JOIN (SELECT @n := 0, @p := NULL, @g := 0) i
     ORDER BY datetime
  ) q
   GROUP BY rnum - grnum
  HAVING COUNT(*) >= 5
     AND TIMESTAMPDIFF(HOUR, MIN(datetime), MAX(datetime)) >= 1
) p

Output:

| TOTAL |
|-------|
|     1 |

Here is SQLFiddle demo

MySQL query for check alerts during a timelapse and condition

2 Answers2