How to Generate a Date Series in PostgreSQL
Last updated July 24, 2026 · By the SaturnSQL team
generate_series() produces a row per interval between two timestamps. Left-join your data onto it to get zero-filled rows for days with no activity, the classic reporting fix.
SELECT generate_series(
'2026-07-01'::date,
'2026-07-31'::date,
'1 day'
)::date AS day;Zero-filled daily counts
SELECT d.day, COUNT(o.id) AS orders
FROM generate_series(current_date - 29, current_date, '1 day') AS d(day)
LEFT JOIN orders o ON o.created_at::date = d.day
GROUP BY d.day
ORDER BY d.day;Run this in SaturnSQL
SaturnSQL is a browser-based SQL editor for teams: shared query library, schema-aware autocomplete, and scheduled exports to Google Sheets and Slack.
Try it free