SQL · Guides · October 29, 2026 · 11 min read

Fill missing dates in SQL: a date spine with generate_series

GROUP BY day only returns the days that exist in the data. If nothing happened on a Tuesday, there is no Tuesday row: a chart draws a straight line across the hole, an average “per day” is divided by too few days, and a 7-row window covers more than seven days. The fix is a date spine (also called a calendar table or date scaffold): a list of every date in the period, built independently of the data. You LEFT JOIN the daily metrics to it and turn the missing values into zeros with COALESCE.

In PostgreSQL the spine is one function call:

SELECT CAST(d.dt AS DATE) AS day
FROM generate_series(DATE '2026-08-01', DATE '2026-08-03', INTERVAL '1 day') AS d(dt);

Both ends are included. generate_series with dates returns timestamps, so cast the value back to DATE before joining it with date columns.

The rest of this guide shows the pattern on a training airline database (flights, bookings and an app event log from summer 2026). Every query was run on that data and the tables show the real output; you can paste them into the Nomto SQL room and check.

The problem: GROUP BY skips empty days

Anadyr (DYR), in the far north-east, has a few departures a week. Count them per day for the week of 27 July:

SELECT CAST(scheduled_departure AS DATE) AS day, COUNT(*) AS departures
FROM flights
WHERE departure_airport = 'DYR'
  AND scheduled_departure >= '2026-07-27'
  AND scheduled_departure < '2026-08-03'
GROUP BY CAST(scheduled_departure AS DATE)
ORDER BY day;

Four rows for a seven-day week. Nothing here says that 27 July, 31 July and 1 August existed and had zero departures.

The averages show the damage. Over the two weeks from 20 July to 2 August:

WITH daily AS (
  SELECT CAST(scheduled_departure AS DATE) AS day, COUNT(*) AS departures
  FROM flights
  WHERE departure_airport = 'DYR'
    AND scheduled_departure >= DATE '2026-07-20'
    AND scheduled_departure < DATE '2026-08-03'
  GROUP BY CAST(scheduled_departure AS DATE)
)
SELECT COUNT(*) AS days_in_result, SUM(departures) AS departures,
  ROUND(AVG(departures), 2) AS naive_avg,
  ROUND(SUM(departures) / 14.0, 2) AS true_avg
FROM daily;

AVG only sees the eight days with flights and reports one departure a day. The true daily average over 14 days is 0.57.

The fix: spine, LEFT JOIN, COALESCE

Put the calendar on the left, the aggregated data on the right:

SELECT CAST(d.dt AS DATE) AS day,
  COALESCE(c.departures, 0) AS departures
FROM generate_series(DATE '2026-07-27', DATE '2026-08-02', INTERVAL '1 day') AS d(dt)
LEFT JOIN (
  SELECT CAST(scheduled_departure AS DATE) AS dep_day, COUNT(*) AS departures
  FROM flights
  WHERE departure_airport = 'DYR'
  GROUP BY CAST(scheduled_departure AS DATE)
) AS c ON c.dep_day = CAST(d.dt AS DATE)
ORDER BY day;

Seven rows, three honest zeros. Three choices make this work:

1. The spine is on the left of a LEFT JOIN. Every calendar day survives; days without data get NULL on the right.
2. COALESCE(..., 0) turns NULL into zero. Only do this for counts and sums, where “no rows” really means zero. For an average price or a conversion rate, “no data” is not the same as 0; leave it NULL.
3. The data is aggregated before the join (the subquery c). The spine then only has to match one row per day.

Two mistakes that bring the gaps back

Filtering the right table in WHERE

If you join raw rows and put the airport filter in WHERE, the empty days disappear again:

SELECT CAST(d.dt AS DATE) AS day, COUNT(f.flight_id) AS departures
FROM generate_series(DATE '2026-07-27', DATE '2026-08-02', INTERVAL '1 day') AS d(dt)
LEFT JOIN flights f ON CAST(f.scheduled_departure AS DATE) = CAST(d.dt AS DATE)
WHERE f.departure_airport = 'DYR'
GROUP BY CAST(d.dt AS DATE)
ORDER BY day;

For an empty day, f.departure_airport is NULL, NULL = 'DYR' is not true, and WHERE throws the row away. The LEFT JOIN has quietly become an inner join. Conditions on the right-hand table belong in the ON clause or inside the subquery.

COUNT(*) instead of COUNT(column)

Move the filter to ON and count both ways:

SELECT CAST(d.dt AS DATE) AS day,
  COUNT(f.flight_id) AS departures,
  COUNT(*) AS count_star
FROM generate_series(DATE '2026-07-27', DATE '2026-08-02', INTERVAL '1 day') AS d(dt)
LEFT JOIN flights f
  ON CAST(f.scheduled_departure AS DATE) = CAST(d.dt AS DATE)
 AND f.departure_airport = 'DYR'
GROUP BY CAST(d.dt AS DATE)
ORDER BY day;

All seven days are back. But COUNT(*) counts the calendar row itself, so every empty day shows 1. COUNT(f.flight_id) counts only non-NULL values and gives the right zero. Aggregating in a subquery first, as in the previous section, avoids both traps.

Taking the range from the data

Hard-coded dates are fine for a report, but often the range should follow the data: from the first to the last event. Compute the bounds in a CTE and feed them to generate_series:

WITH bounds AS (
  SELECT CAST(MIN(scheduled_departure) AS DATE) AS first_day,
    CAST(MAX(scheduled_departure) AS DATE) AS last_day
  FROM flights
  WHERE departure_airport = 'DYR'
),
spine AS (
  SELECT CAST(d.dt AS DATE) AS day
  FROM bounds b
  CROSS JOIN generate_series(b.first_day, b.last_day, INTERVAL '1 day') AS d(dt)
),
daily AS (
  SELECT CAST(scheduled_departure AS DATE) AS day, COUNT(*) AS departures
  FROM flights
  WHERE departure_airport = 'DYR'
  GROUP BY CAST(scheduled_departure AS DATE)
)
SELECT COUNT(*) AS days,
  COUNT(daily.day) AS days_with_flights,
  COUNT(*) - COUNT(daily.day) AS empty_days
FROM spine
LEFT JOIN daily ON daily.day = spine.day;

Anadyr’s schedule runs from 12 July to 10 September: 61 days, of which 25 have no departure. In PostgreSQL a function in FROM can refer to columns of tables listed before it, so generate_series(b.first_day, ...) works without writing LATERAL explicitly.

Be careful with “first to last event” on metrics where the edges matter: if the period should be a full month, take the month’s bounds, not the data’s, or the first quiet days of the month will be missing again.

Zero-filled grids: dates × categories

A spine can have more than one dimension. To report a metric for every day and every platform, CROSS JOIN the calendar with the list of categories, then LEFT JOIN the data on both keys. Here are payment gateway timeout errors, which only ever hit the iOS app:

SELECT CAST(d.dt AS DATE) AS day, p.platform,
  COALESCE(e.errors, 0) AS errors
FROM generate_series(DATE '2026-07-23', DATE '2026-07-24', INTERVAL '1 day') AS d(dt)
CROSS JOIN (VALUES ('android'), ('ios'), ('web')) AS p(platform)
LEFT JOIN (
  SELECT CAST(event_time AS DATE) AS day, platform, COUNT(*) AS errors
  FROM app_events
  WHERE event_name = 'error'
    AND (CAST(properties AS JSON) ->> 'code') = 'PAYMENT_GATEWAY_TIMEOUT'
  GROUP BY CAST(event_time AS DATE), platform
) AS e ON e.day = CAST(d.dt AS DATE) AND e.platform = p.platform
ORDER BY day, p.platform;

Without the grid, this query would return a single row. With it, a chart has a line for each platform and the last day of the incident is visible as a drop to zero. The category list can come from a VALUES list, as here, or from SELECT DISTINCT platform FROM ... when the set is not fixed.

Hourly spines

The same function makes a series of hours: give it timestamps and a one-hour step. When did that timeout incident start on 21 July?

SELECT h.hour_start, COALESCE(e.errors, 0) AS errors
FROM generate_series(TIMESTAMP '2026-07-21 05:00', TIMESTAMP '2026-07-21 11:00',
                     INTERVAL '1 hour') AS h(hour_start)
LEFT JOIN (
  SELECT date_trunc('hour', event_time) AS hour_start, COUNT(*) AS errors
  FROM app_events
  WHERE event_name = 'error'
    AND (CAST(properties AS JSON) ->> 'code') = 'PAYMENT_GATEWAY_TIMEOUT'
  GROUP BY date_trunc('hour', event_time)
) AS e ON e.hour_start = h.hour_start
ORDER BY h.hour_start;

The zeros before 09:00 are the evidence that the problem started in the 09:00 hour, not that the data is missing. For an hour-of-day profile across many days, a series of numbers is enough: generate_series(0, 23).

Why a spine fixes rolling windows

A 7-day rolling sum written as ROWS BETWEEN 6 PRECEDING AND CURRENT ROW assumes one row per day. On a series with holes it silently stretches over more than seven days. iOS “price changed” errors are rare, and on 9 July there were none:

WITH daily AS (
  SELECT CAST(event_time AS DATE) AS day, COUNT(*) AS errors
  FROM app_events
  WHERE platform = 'ios' AND event_name = 'error'
    AND (CAST(properties AS JSON) ->> 'code') = 'PRICE_CHANGED'
  GROUP BY CAST(event_time AS DATE)
),
spine AS (
  SELECT CAST(d.dt AS DATE) AS day, COALESCE(daily.errors, 0) AS errors
  FROM generate_series(DATE '2026-07-01', DATE '2026-08-10', INTERVAL '1 day') AS d(dt)
  LEFT JOIN daily ON daily.day = CAST(d.dt AS DATE)
),
naive AS (
  SELECT day,
    SUM(errors) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS naive_7
  FROM daily
),
rolled AS (
  SELECT day, errors,
    SUM(errors) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS spine_7
  FROM spine
)
SELECT r.day, r.errors, n.naive_7, r.spine_7
FROM rolled r
LEFT JOIN naive n ON n.day = r.day
WHERE r.day BETWEEN '2026-07-08' AND '2026-07-14'
ORDER BY r.day;

From 10 July on, the naive window reaches back eight calendar days, because one of the “six preceding rows” is a day earlier than it should be, and its values are too high. The spine version always covers exactly seven days, and 9 July itself gets a value instead of vanishing. Note that the WHERE filter sits in the outer query, after the windows have been computed over the whole series; for why that matters, see our guide to running totals in SQL.

Listing the missing dates

Sometimes the question is the gaps themselves: which days have no data? Keep only spine rows without a match, here with NOT EXISTS:

SELECT CAST(d.dt AS DATE) AS missing_day
FROM generate_series(DATE '2026-07-01', DATE '2026-08-10', INTERVAL '1 day') AS d(dt)
WHERE NOT EXISTS (
  SELECT 1
  FROM app_events e
  WHERE e.platform = 'ios' AND e.event_name = 'error'
    AND (CAST(e.properties AS JSON) ->> 'code') = 'PRICE_CHANGED'
    AND CAST(e.event_time AS DATE) = CAST(d.dt AS DATE)
)
ORDER BY 1;

This is also the standard data-quality check for pipelines: run it against a table that should have rows every day (a daily load, a sensor feed) and alert when the list is not empty. For runs of consecutive missing days, combine it with the gaps and islands technique.

A permanent calendar table

generate_series is perfect inside a query. In a warehouse you will often also see a stored calendar table (dim_date), one row per date for many years, with extra columns: day of week, ISO week, month start, fiscal period, public holiday flags. It is built once, usually with the same generate_series, and joined like any other table. It pays off when many reports need the same attributes, or when your database lacks a series function.

Checklist

• Spine on the left of a LEFT JOIN.
• Aggregate the data before joining, one row per day (or per day and category).
• Filters on the data go into the subquery or ON, never WHERE.
• COALESCE(x, 0) for counts and sums only; rates and averages stay NULL on empty days.
• Cast the series value to DATE when joining with dates.
• Choose the range on purpose: report period or data bounds.
• Build rolling windows and island logic on the spine, not on the sparse data.

Every example in this guide uses tables from the Nomto SQL course, so you can run them in the SQL room, change the airport or the error code, and see the zeros appear.

FAQ

How do I fill missing dates in SQL?

Generate a list of all dates in the period (in PostgreSQL with generate_series(start, end, INTERVAL '1 day')), LEFT JOIN your daily aggregates to it, and wrap the metric in COALESCE(metric, 0).

What is a date spine?

A date spine is a table or subquery with one row per date (or hour, week, month) for the whole period, created independently of the data. Joining facts to it guarantees that every period appears in the result, even when nothing happened.

Why does my LEFT JOIN still drop the empty days?

Most likely a condition on the right-hand table is in WHERE. For empty days that column is NULL, the condition is not true, and the row is removed. Move the condition into ON or into the subquery that aggregates the data.

Should missing days be zero or NULL?

Zero for counts and sums (no flights means 0 flights). NULL for averages, rates and prices, where an empty day has no value rather than a value of zero.

How do I generate a date series without generate_series?

Use a recursive CTE that starts at the first date and adds one day until the end date, or a stored calendar table. Most analytical databases also have their own series or range function.