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 *
FROM generate_series(timestamp '2023-10-21 00:00'
, timestamp '2023-10-21 00:25'
, interval '15 min')
ORDER BY 1; -- optional
| generate_series |
|---|
| 2023-10-21 00:00:00 |
| 2023-10-21 00:15:00 |
SELECT 2
-- get ceiling for end value with date_bin()
SELECT date_bin(interval '15 min'
, timestamp '2023-10-21 00:25'
, timestamp '2023-10-21 00:00') + interval '15 min';
| ?column? |
|---|
| 2023-10-21 00:30:00 |
SELECT 1
-- fails for exact step
SELECT generate_series(a, date_bin(intv, z, a) + intv, intv)
FROM (
VALUES (timestamp '2023-10-21 00:00'
, timestamp '2023-10-21 00:25'
, interval '15 min')
) input(a, z, intv);
| generate_series |
|---|
| 2023-10-21 00:00:00 |
| 2023-10-21 00:15:00 |
| 2023-10-21 00:30:00 |
SELECT 3
-- works: increment unless we hit the interval
SELECT generate_series(a, CASE WHEN date_bin(intv, z, a) = z THEN z ELSE date_bin(intv, z, a) + intv END, intv)
FROM (
VALUES (timestamp '2023-10-21 00:00'
, timestamp '2023-10-21 00:29'
, interval '15 min')
) input(a, z, intv);
| generate_series |
|---|
| 2023-10-21 00:00:00 |
| 2023-10-21 00:15:00 |
| 2023-10-21 00:30:00 |
SELECT 3