How to Generate a Date Series in PostgreSQL

generate_series is a set-returning function, so it produces rows rather than a single value. The step is an interval, so '1 hour', '15 minutes', and '1 month' all work. Casting to date drops the time component; leave the cast off if you want timestamps. The end value is included when it lands exactly on a step.

SELECT generate_series(
  '2026-07-01'::date,
  '2026-07-31'::date,
  '1 day'
)::date AS day;

Zero-filled daily counts

This is the fix for the missing-days problem: a plain GROUP BY over orders returns no row at all for a day with no orders, which leaves gaps in charts and breaks running totals. Generating the days first and left-joining guarantees one row per day. COUNT(o.id) rather than COUNT(*) is what makes empty days show 0 instead of 1.

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;