yglodt yglodt -4 years ago 136
SQL Question

SQL to find values higher than a threshold for a certain time

Consider the following table:

create table measurement (
datetime timestamp,
temperature numeric(5,2)
);


I would like to create an
SQL
-query in
PostgreSQL
which extracts the rows where the temperature was higher than 50 °C for at least 30 minutes, ideally knowing from when until when the temperature was actually above 50 °C. The example data is here:

datetime temperature
------------------- -----------
2017-03-15 19:00:10 49.56
2017-03-15 19:15:10 52.81
2017-03-15 19:30:10 49.00
2017-03-15 19:45:10 52.88
2017-03-15 20:00:10 49.56
2017-03-15 20:15:10 49.13
2017-03-15 20:30:10 51.31 <--
2017-03-15 20:45:10 52.06 <--
2017-03-15 21:00:10 50.50 <--
2017-03-15 21:15:10 50.50 <--
2017-03-15 21:30:10 49.38
2017-03-15 21:45:10 47.44
2017-03-15 22:00:10 46.19
2017-03-15 22:15:10 45.44
2017-03-15 22:30:10 50.25
2017-03-15 22:45:10 48.56
2017-03-15 23:00:10 51.25 <--
2017-03-15 23:15:10 50.44 <--
2017-03-15 23:30:10 50.63 <--
2017-03-15 23:45:10 46.75

Answer Source

Gordon's solution can be simplified to a single OLAP-function:

select min(datetime), max(datetime), count(*) as numrecs, avg(temperature)
from
 (
   select datetime, temperature, 
      -- previous time when temperature was < 50
      -- same time for all rows with a temp >= 50
      max(case when temperature < 50 then datetime end)
      over (order by datetime
            rows unbounded preceding) as prevlow
   from measurement
 ) as dt
where temperature >= 50
group by prevlow
having max(datetime) >= min(datetime) + interval '30' minute;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download