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?.
select version();
| version |
|---|
| PostgreSQL 14.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-4), 64-bit |
create table inputtable (equipment_id int, telemetry_time timestamp);
insert into inputtable values
(2 ,'2022-06-16 04:00:00'),
(2 ,'2022-06-16 04:30:00'),
(2 ,'2022-06-16 06:00:00'),
(2 ,'2022-06-16 06:30:00'),
(2 ,'2022-06-16 08:00:00'),
(2 ,'2022-06-16 08:30:00'),
(2 ,'2022-06-16 20:00:00'),
(2 ,'2022-06-16 23:30:00'),
(2 ,'2022-06-17 02:30:00'),
(2 ,'2022-06-17 03:00:00'),
(2 ,'2022-06-17 10:30:00');
select * from inputtable;
11 rows affected
| equipment_id | telemetry_time |
|---|---|
| 2 | 2022-06-16 04:00:00 |
| 2 | 2022-06-16 04:30:00 |
| 2 | 2022-06-16 06:00:00 |
| 2 | 2022-06-16 06:30:00 |
| 2 | 2022-06-16 08:00:00 |
| 2 | 2022-06-16 08:30:00 |
| 2 | 2022-06-16 20:00:00 |
| 2 | 2022-06-16 23:30:00 |
| 2 | 2022-06-17 02:30:00 |
| 2 | 2022-06-17 03:00:00 |
| 2 | 2022-06-17 10:30:00 |
with date_shift as (
select equipment_id, telemetry_time,
(telemetry_time - interval '8 hours')::date as tdate
from inputtable
)
select equipment_id, telemetry_time,
row_number() over (partition by equipment_id, tdate
order by telemetry_time)
from date_shift;
| equipment_id | telemetry_time | row_number |
|---|---|---|
| 2 | 2022-06-16 04:00:00 | 1 |
| 2 | 2022-06-16 04:30:00 | 2 |
| 2 | 2022-06-16 06:00:00 | 3 |
| 2 | 2022-06-16 06:30:00 | 4 |
| 2 | 2022-06-16 08:00:00 | 1 |
| 2 | 2022-06-16 08:30:00 | 2 |
| 2 | 2022-06-16 20:00:00 | 3 |
| 2 | 2022-06-16 23:30:00 | 4 |
| 2 | 2022-06-17 02:30:00 | 5 |
| 2 | 2022-06-17 03:00:00 | 6 |
| 2 | 2022-06-17 10:30:00 | 1 |