Postgres 15

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 mytable (
  dtime timestamp
);

insert into mytable values
('2023-07-01 10:00:00'),
('2023-07-01 10:00:01'),
('2023-07-01 10:01:00'),
('2023-07-01 10:15:00'),
('2023-07-01 10:09:59');

CREATE TABLE

INSERT 0 5

select dtime, (cast(extract(minute from dtime) as int)/10 + 1) * 10 as nearest_10m
from mytable;

dtime
nearest_10m

2023-07-01 10:00:00
10

2023-07-01 10:00:01
10

2023-07-01 10:01:00
10

2023-07-01 10:15:00
20

2023-07-01 10:09:59
10

SELECT 5

select dtime,
       (date_trunc('hour', dtime) +
        (cast(extract(minute from dtime) as int)/10 + 1) * 10 * interval '1 minute' 
       ) as rounded_dtime
from mytable

dtime
rounded_dtime

2023-07-01 10:00:00
2023-07-01 10:10:00

2023-07-01 10:00:01
2023-07-01 10:10:00

2023-07-01 10:01:00
2023-07-01 10:10:00

2023-07-01 10:15:00
2023-07-01 10:20:00

2023-07-01 10:09:59
2023-07-01 10:10:00

SELECT 5

dtime	nearest_10m
2023-07-01 10:00:00	10
2023-07-01 10:00:01	10
2023-07-01 10:01:00	10
2023-07-01 10:15:00	20
2023-07-01 10:09:59	10

dtime	rounded_dtime
2023-07-01 10:00:00	2023-07-01 10:10:00
2023-07-01 10:00:01	2023-07-01 10:10:00
2023-07-01 10:01:00	2023-07-01 10:10:00
2023-07-01 10:15:00	2023-07-01 10:20:00
2023-07-01 10:09:59	2023-07-01 10:10:00