PostgreSQL: Fill Missing Dates

When you’re analyzing time series data—sales by day, events by hour, user activity by week—you often encounter gaps. Maybe there were no sales on certain dates, or no events logged during certain hours. When you visualize or analyze that data, those missing dates create misleading gaps in charts and skew your calculations. You need to fill in those missing periods with appropriate default values (usually zero or NULL, depending on context).

PostgreSQL’s generate_series() function is perfect for this. It creates a complete sequence of dates, and then you join your actual data against it, filling gaps with defaults. It’s a pattern you’ll use constantly in time series analysis.

Quick Answer: Use generate_series

To fill missing dates:

WITH date_series AS (
    SELECT generate_series(
        (SELECT MIN(date) FROM data),
        (SELECT MAX(date) FROM data),
        INTERVAL '1 day'
    )::DATE as date
)
SELECT
    ds.date,
    COALESCE(d.value, 0) as value
FROM date_series ds
LEFT JOIN data d ON ds.date = d.date
ORDER BY ds.date;

Returns every date with data or zero.

How It Works

generate_series() creates all dates in range. LEFT JOIN brings in existing data, or COALESCE fills with default.

Different Approaches & When to Use Them

Approach 1: Daily Completeness

WITH all_dates AS (
    SELECT generate_series('2026-01-01'::DATE, '2026-12-31'::DATE, '1 day'::INTERVAL)::DATE as date
)
SELECT ad.date, COALESCE(sales.amount, 0)
FROM all_dates ad
LEFT JOIN sales ON ad.date = sales.date;

Approach 2: With Default Values

SELECT
    ds.date,
    COALESCE(d.value, 0) as value,
    COALESCE(d.status, 'missing') as status
FROM (
    SELECT generate_series(start_date, end_date, '1 day'::INTERVAL)::DATE
) ds(date)
LEFT JOIN data d ON ds.date = d.date;

Approach 3: Hourly Completeness

WITH hour_series AS (
    SELECT generate_series(start_time, end_time, '1 hour'::INTERVAL) as hour
)
SELECT hs.hour, COALESCE(COUNT(events.id), 0)
FROM hour_series hs
LEFT JOIN events ON DATE_TRUNC('hour', events.created_at) = hs.hour
GROUP BY hs.hour;

Real-World Example: Daily Report with Zeros

WITH calendar AS (
    SELECT generate_series(
        CURRENT_DATE - INTERVAL '30 days',
        CURRENT_DATE,
        '1 day'::INTERVAL
    )::DATE as date
)
SELECT
    c.date,
    COALESCE(SUM(sales.amount), 0) as daily_sales,
    COALESCE(COUNT(sales.id), 0) as order_count
FROM calendar c
LEFT JOIN sales ON c.date = sales.date::DATE
GROUP BY c.date
ORDER BY c.date DESC;

FAQ

Q: How do I fill hourly gaps? A: Use INTERVAL ‘1 hour’ in generate_series.

Q: Performance for large ranges? A: Still fast. generate_series is optimized.

Q: How do I exclude weekends? A: Add WHERE EXTRACT(DOW FROM date) NOT IN (0,6).

Q: What about hierarchical fills (days -> months)? A: Use different INTERVAL values or multiple generate_series.

Wrapping Up

Use generate_series() to create all dates, then LEFT JOIN with actual data. Fill gaps with COALESCE and defaults.