Calendars and Time Buckets

generate_series(start, stop, step) returns a row per value, for numbers, dates and timestamps; as a calendar it defines report buckets independently of the data. date_bin (PostgreSQL 14 1,289 ) assigns timestamps to buckets of any width from a chosen origin:

SELECT w::date AS week_start, count(o.order_id) AS orders
FROM generate_series(timestamptz '2026-06-01', '2026-06-29', '7 days') AS w
LEFT JOIN orders o ON o.order_ts >= w AND o.order_ts < w + interval '7 days'
GROUP BY w ORDER BY w;
SELECT date_bin('7 days', order_ts, timestamptz '2026-06-01') AS bucket, count(*)
FROM orders WHERE order_ts >= '2026-06-01'
GROUP BY 1 ORDER BY 1;

 week_start | orders
------------+--------
 2026-06-01 |   1253
 2026-06-08 |   1255
 2026-06-15 |   1325
 2026-06-22 |   1278
 2026-06-29 |    387
...
 2026-06-29 00:00:00+00 |   387

Both agree; the last bucket is short because the data ends on 30 June. date_trunc('week', ...) gives ISO weeks, but only date_bin handles 15-minute or 10-day buckets. The calendar form also keeps buckets with no rows.