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 original as 
    select '1/4/2020 10:00'::timestamp as Moves_TS union all
    select '1/4/2020 10:25'::timestamp as Moves_TS union all
    select '1/4/2020 10:42'::timestamp as Moves_TS union all
    select '1/4/2020 10:56'::timestamp as Moves_TS union all
    select '1/4/2020 10:59'::timestamp as Moves_TS union all
    select '1/4/2020 11:02'::timestamp as Moves_TS union all
    select '1/4/2020 11:24'::timestamp as Moves_TS union all
    select '1/4/2020 11:43'::timestamp as Moves_TS union all
    select '1/4/2020 11:55'::timestamp as Moves_TS union all
    select '1/4/2020 12:26'::timestamp as Moves_TS union all
    select '1/4/2020 12:29'::timestamp as Moves_TS 
11 rows affected

select *
from original


moves_ts


2020-04-01 10:00:00

2020-04-01 10:25:00

2020-04-01 10:42:00

2020-04-01 10:56:00

2020-04-01 10:59:00

2020-04-01 11:02:00

2020-04-01 11:24:00

2020-04-01 11:43:00

2020-04-01 11:55:00

2020-04-01 12:26:00

2020-04-01 12:29:00


select min(moves_ts) as start_ts, max(moves_ts) as end_ts
from (select o.*,
             count(prev_moves_ts) filter (where moves_ts > prev_moves_ts + interval '30 minute') over (order by moves_ts) as grp
      from (select o.*, lag(moves_ts) over (order by moves_ts) as prev_moves_ts
            from original o
           ) o
     ) o
group by grp;


start_ts
end_ts


2020-04-01 10:00:00
2020-04-01 11:55:00

2020-04-01 12:26:00
2020-04-01 12:29:00


with islands as (
      select min(moves_ts) as start_ts, max(moves_ts) as end_ts
      from (select o.*,
                   count(prev_moves_ts) filter (where moves_ts > prev_moves_ts + interval '30 minute') over (order by moves_ts) as grp
            from (select o.*, lag(moves_ts) over (order by moves_ts) as prev_moves_ts
                  from original o
                 ) o
           ) o
      group by grp
     )
select hh.hh,
       sum( least(hh.hh + interval '1 hour', i.end_ts) -
            greatest(hh.hh, i.start_ts)
          ) as duration           
from (select generate_series(date_trunc('hour', min(moves_ts)),
                             date_trunc('hour', max(moves_ts)),
                             interval '1 hour'
                            ) hh
      from original o
     ) hh left join
     islands i
     on i.start_ts < hh.hh + interval '1 hour' and
        i.end_ts >= hh.hh
group by hh.hh
order by hh.hh;


hh
duration


2020-04-01 10:00:00
01:00:00

2020-04-01 11:00:00
00:55:00

2020-04-01 12:00:00
00:03:00

start_ts	end_ts
2020-04-01 10:00:00	2020-04-01 11:55:00
2020-04-01 12:26:00	2020-04-01 12:29:00

hh	duration
2020-04-01 10:00:00	01:00:00
2020-04-01 11:00:00	00:55:00
2020-04-01 12:00:00	00:03:00