How do I write this Postgres Query?

user_578825 0
Viewed 58

For my data, I have a value which toggles between 1 and 0. I need to find the "time deltas" for the segments which have value 0. I think this will use the min(), max() functions. Possibly rank.

Input:

Time_stamp            , value
'2018-01-01 10:15:00' , 1
'2018-01-01 10:16:00' , 1
'2018-01-01 10:17:00' , 0
'2018-01-04 10:18:00' , 0
'2018-01-04 10:19:00' , 1
'2018-01-04 10:20:00' , 1
'2018-01-06 10:21:00' , 1
'2018-01-06 10:22:00' , 0
'2018-01-06 10:23:00' , 0
'2018-01-07 10:24:00' , 1
'2018-01-07 10:25:00' , 1
'2018-01-07 10:26:00' , 1


Output
id | max                  |  min  
1  |'2018-01-04 10:18 AM' |'2018-01-01 10:17 AM'
2  |'2018-01-06 10:23 AM' |'2018-01-06 10:22 AM'

Here is the SQL to create the data

CREATE TABLE test_ts (
    time_stamp     TIMESTAMPTZ,
    val    integer
);
insert into test_ts
SELECT * FROM (
 VALUES
  ( '2018-01-01 10:15:00'::timestamptz , 1),
   ( '2018-01-01 10:16:00'::timestamptz  , 1),
   ( '2018-01-01 10:17:00'::timestamptz  , 0),
   ( '2018-01-04 10:18:00'::timestamptz  , 0),
   ( '2018-01-04 10:19:00'::timestamptz  , 1),
   ( '2018-01-04 10:20:00'::timestamptz  , 1),
   ( '2018-01-06 10:21:00'::timestamptz  , 1),
   ( '2018-01-06 10:22:00'::timestamptz  , 0),
   ( '2018-01-06 10:23:00'::timestamptz  , 0),
   ( '2018-01-07 10:24:00'::timestamptz  , 1),
   ( '2018-01-07 10:25:00'::timestamptz  , 1),
   ( '2018-01-07 10:26:00'::timestamptz  , 1)) testAlias;
rank gaps-and-islands postgresql sql
1 Answers

This is a so-called gaps & islands problem. Use two counters, one for all rows, one per value, subtract and you get group keys per value:

select max(time_stamp), min(time_stamp)
from
(
  select 
    time_stamp,
    val,
    row_number() over (order by time_stamp) -
    row_number() over (partition by val order by time_stamp) as grp
  from test_ts
) groups
where val = 0
group by grp
order by min(time_stamp);

Rextester demo: http://rextester.com/WIUX68902

user_22707620
Related