Postgres 12

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 event
(
    id int primary key,
    time   time,
    status integer
);

insert into event
values
(1, '12:00', 0),
(2, '13:00', 8),
(3, '14:00', 4),
(4, '15:00', 2),
(5, '16:00', 0),
(6, '17:00', 9),
(7, '18:00', 5),
(8, '19:00', 8),
(9, '20:00', 0),
(10, '21:00', 1),
(11, '22:00', 3),
(12, '23:00', 0);
12 rows affected

  select
      time, status,
      lead(time) over (order by time) as next_time,
      sum(case when status = 0 then 1 else 0 end) over (order by id) as grp
  from
      event


time
status
next_time
grp


12:00:00
0
13:00:00
1

13:00:00
8
14:00:00
1

14:00:00
4
15:00:00
1

15:00:00
2
16:00:00
1

16:00:00
0
17:00:00
2

17:00:00
9
18:00:00
2

18:00:00
5
19:00:00
2

19:00:00
8
20:00:00
2

20:00:00
0
21:00:00
3

21:00:00
1
22:00:00
3

22:00:00
3
23:00:00
3

23:00:00
0
null
4


with ev as
(
  select
      time, status,
      lead(time) over (order by time) as next_time,
      sum(case when status = 0 then 1 else 0 end) over (order by id) as grp
  from
      event
)
select 
    min(time) as min_time,
    max(next_time) as max_time,
    array_agg(row(time, status)) filter (where status <> 0) as events
from 
    ev
group by
    grp
order by
    grp;


min_time
max_time
events


12:00:00
16:00:00
{"(13:00:00,8)","(14:00:00,4)","(15:00:00,2)"}

16:00:00
20:00:00
{"(17:00:00,9)","(18:00:00,5)","(19:00:00,8)"}

20:00:00
23:00:00
{"(21:00:00,1)","(22:00:00,3)"}

23:00:00
null
null

time	status	next_time	grp
12:00:00	0	13:00:00	1
13:00:00	8	14:00:00	1
14:00:00	4	15:00:00	1
15:00:00	2	16:00:00	1
16:00:00	0	17:00:00	2
17:00:00	9	18:00:00	2
18:00:00	5	19:00:00	2
19:00:00	8	20:00:00	2
20:00:00	0	21:00:00	3
21:00:00	1	22:00:00	3
22:00:00	3	23:00:00	3
23:00:00	0	null	4

min_time	max_time	events
12:00:00	16:00:00	{"(13:00:00,8)","(14:00:00,4)","(15:00:00,2)"}
16:00:00	20:00:00	{"(17:00:00,9)","(18:00:00,5)","(19:00:00,8)"}
20:00:00	23:00:00	{"(21:00:00,1)","(22:00:00,3)"}
23:00:00	null	null