Extract MIN and MAX values related to datetime values on Postgres with a condition postgresql-CodePudding

I am trying to query a table that has a collection of values recorded over time. I would like to produce a result set that grabs the first date and the last date where the value>170 consecutively.

I think I need to use the MIN and MAX functions and some subqueries to achieve this, but I'm just not getting the results I need.

Is there an effective way to achieve the results below on PostgreSQL?

Here's a sample of my data set :

date	value
2022-02-07 15:30:30	169.6
2022-02-07 15:30:55	171
2022-02-07 15:31:10	170.9
2022-02-07 15:31:50	171.1
2022-02-07 15:32:00	172
2022-02-07 15:32:45	168
2022-02-07 15:33:20	168.7
2022-02-07 15:34:10	173.7
2022-02-07 15:34:55	171.5
2022-02-07 15:35:20	171.7
2022-02-07 15:36:05	163.5

the expected result:

Date_start	Date_end	Value_max
2022-02-07 15:30:55	2022-02-07 15:32:00	172
2022-02-07 15:34:10	2022-02-07 15:35:20	173.7

CodePudding user response：

This is a Gaps & Islands problem. You can use the traditional solution: using LAG() or LEAD().

For example:

select min(date), max(date), max(value)
from (
  select *, sum(i) over(order by date) as g
  from (
    select *,
      case when (lag(value) over(order by date) > 170) <> (value > 170)
           then 1 else 0
      end as i
    from mytable
  ) x
) y
group by g
having max(value) > 170
order by g

Result:

 min                  max                  max   
 -------------------- -------------------- ----- 
 2022-02-07 15:30:55  2022-02-07 15:32:00  172.0 
 2022-02-07 15:34:10  2022-02-07 15:35:20  173.7

See running example at db<>fiddle.