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 T(
time interval,
state int
);
INSERT INTO T VALUES ('01:00:00',0);
INSERT INTO T VALUES ('01:01:00',1);
INSERT INTO T VALUES ('01:02:35',1);
INSERT INTO T VALUES ('01:03:20',0);
INSERT INTO T VALUES ('01:04:00',0);
INSERT INTO T VALUES ('01:05:00',0);
INSERT INTO T VALUES ('01:06:00',1);
INSERT INTO T VALUES ('01:07:00',1);
INSERT INTO T VALUES ('01:08:00',1);
INSERT INTO T VALUES ('01:09:10',0);
INSERT INTO T VALUES ('01:10:00',0);
INSERT INTO T VALUES ('01:11:00',1);
INSERT INTO T VALUES ('01:12:45',0);
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
WITH CTE AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY condition ORDER BY time) -
ROW_NUMBER() OVER ( ORDER BY time) grp
FROM (
SELECT *,
(CASE WHEN state = 1 OR LAG(state,1,state) OVER(ORDER BY TIME) = 1 THEN 1 END) condition
FROM T
) t1
)
SELECT MIN(time) starttime ,
MAX(time) endtime ,
MAX(time) - MIN(time) duration
FROM CTE
WHERE condition IS NOT NULL
GROUP BY grp
ORDER BY 1
| starttime | 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 |