By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
create table test (time time, state integer) ;
insert into test values
('01:00:00', 0),
('01:01:00', 1),
('01:02:35', 1),
('01:03:20', 0),
('01:04:00', 0),
('01:05:00', 0),
('01:06:00', 1),
('01:07:00', 1),
('01:08:00', 1),
('01:09:10', 0),
('01:10:00', 0),
('01:11:00', 1),
('01:12:45', 0);
13 rows affected
WITH list AS
( SELECT time AS starttime
, state
, CASE
WHEN state = 0
THEN min(time) FILTER (WHERE state = 1) OVER (ORDER BY time ASC ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING)
ELSE min(time) FILTER (WHERE state = 0) OVER (ORDER BY time ASC ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING)
END AS endtime
FROM test AS t
)
SELECT min(starttime)
, endtime
, endtime - min(starttime) AS duration
FROM list
WHERE state = 1
GROUP BY endtime
| min | endtime | duration |
|---|---|---|
| 01:01:00 | 01:03:20 | 00:02:20 |
| 01:06:00 | 01:09:10 | 00:03:10 |
| 01:11:00 | 01:12:45 | 00:01:45 |