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
jsonb_agg(
jsonb_build_object(
'day',tm::date,
'hour',EXTRACT(HOUR FROM tm)))
FROM generate_series(
'2021-08-01 00:00:00'::timestamp,
'2021-08-01 23:59:59'::timestamp,interval '1 hour') j (tm);
| jsonb_agg |
|---|
| [{"day": "2021-08-01", "hour": 0}, {"day": "2021-08-01", "hour": 1}, {"day": "2021-08-01", "hour": 2}, {"day": "2021-08-01", "hour": 3}, {"day": "2021-08-01", "hour": 4}, {"day": "2021-08-01", "hour": 5}, {"day": "2021-08-01", "hour": 6}, {"day": "2021-08-01", "hour": 7}, {"day": "2021-08-01", "hour": 8}, {"day": "2021-08-01", "hour": 9}, {"day": "2021-08-01", "hour": 10}, {"day": "2021-08-01", "hour": 11}, {"day": "2021-08-01", "hour": 12}, {"day": "2021-08-01", "hour": 13}, {"day": "2021-08-01", "hour": 14}, {"day": "2021-08-01", "hour": 15}, {"day": "2021-08-01", "hour": 16}, {"day": "2021-08-01", "hour": 17}, {"day": "2021-08-01", "hour": 18}, {"day": "2021-08-01", "hour": 19}, {"day": "2021-08-01", "hour": 20}, {"day": "2021-08-01", "hour": 21}, {"day": "2021-08-01", "hour": 22}, {"day": "2021-08-01", "hour": 23}] |