Skip to content
GreenSnippets

Fill in missing dates with generate_series

Produce a row for every day in a range and left join your data so days with no activity show zero.

SQL
SELECT
  d::date AS day,
  COALESCE(COUNT(o.id), 0) AS orders
FROM generate_series(
  date '2025-01-01',
  date '2025-01-31',
  interval '1 day'
) AS d
LEFT JOIN orders AS o
  ON o.created_at >= d AND o.created_at < d + interval '1 day'
GROUP BY d
ORDER BY d;

Example / expected output

Output
-- 31 rows, one per day, with 0 where there were no orders.

How it works

Charts and moving averages break when days with no rows are simply absent. generate_series creates the complete calendar, and LEFT JOIN plus COALESCE turns missing data into zeros.

This snippet has been reviewed but was not executed by an automated test. Test it in your own environment before relying on it.

Related snippets