Find Missing Dates in SQL: Gap Detection and Filling (PostgreSQL, MySQL, BigQuery)

The Problem

Your daily sales table has rows only when something sold. A dashboard grouped by day therefore skips June 14th entirely — the chart draws a line through a day that never existed. The fix is always the same shape: generate the complete date spine first, then LEFT JOIN your data to it.

PostgreSQL: generate_series

The cleanest syntax anywhere. Full date spine joined to nulls = the report you actually want:

SELECT d::date AS day, COALESCE(SUM(s.amount), 0) AS revenue FROM generate_series('2026-01-01'::date, '2026-06-30'::date, '1 day') AS d LEFT JOIN sales s ON s.created_at::date = d::date GROUP BY d ORDER BY d;

To find ONLY the missing days, filter after the join: WHERE s.amount IS NULL.

MySQL 8: Recursive CTE Calendar

MySQL has no generate_series, so build the spine with a recursive CTE:

WITH RECURSIVE dates AS (SELECT DATE('2026-01-01') AS d UNION ALL SELECT DATE_ADD(d, INTERVAL 1 DAY) FROM dates WHERE d < DATE('2026-06-30')) SELECT dates.d, COALESCE(SUM(s.amount), 0) AS revenue FROM dates LEFT JOIN sales s ON DATE(s.created_at) = dates.d GROUP BY dates.d ORDER BY dates.d;

One MySQL gotcha: the default recursion limit (cte_max_recursion_depth = 1000) covers ~3 years of days, but a multi-year minute-level spine will hit it. Raise it per-session if needed: SET cte_max_recursion_depth = 100000;

BigQuery: UNNEST with GENERATE_DATE_ARRAY

SELECT day, COALESCE(SUM(s.amount), 0) AS revenue FROM UNNEST(GENERATE_DATE_ARRAY('2026-01-01', '2026-06-30')) AS day LEFT JOIN sales s ON DATE(s.created_at) = day GROUP BY day ORDER BY day;

Finding Gaps Instead of Filling Them

When you only want to know WHERE the holes are (data-pipeline monitoring, for example), a window-function approach avoids materializing a spine:

SELECT created_at::date AS gap_start, LEAD(created_at::date) OVER (ORDER BY created_at::date) AS gap_end FROM (SELECT DISTINCT created_at::date FROM sales) t WHERE LEAD(created_at::date) OVER (ORDER BY created_at::date) - created_at::date > 1;

Each row shows a gap boundary: last present day, next present day. Gaps of exactly 1 day difference mean no missing days between them — everything larger is a hole.

The Calendar Table Recommendation

If you write gap queries more than once a month, stop generating spines per query and create a permanent calendar table (day, week, month, quarter, is_holiday flags) with 50-100 years of rows. Queries become plain LEFT JOINs, every BI tool understands them, and you get business-day logic (holidays, fiscal weeks) for free. The generate_series one-liner is for ad-hoc; the calendar table is for production.

Common Mistakes

• Timezones: date_trunc comparisons across timezone boundaries create phantom missing days. Decide the reporting timezone and convert explicitly before grouping.
• Joining on the wrong side: spine must be the LEFT table. Reversed joins return the same gap you started with.
• Forgetting HAVING: when you only want missing days from a spine query, WHERE data IS NULL filters rows, HAVING SUM(...) IS NULL filters groups — pick based on whether you grouped first.

Try It Yourself

Use our free SQL generator — type "daily revenue with missing days as zero for PostgreSQL" and it writes the generate_series version for your table.