PostgreSQL generate_series() examples for daily, weekly, monthly, and hourly date ranges, calendar tables, missing-date reporting, and business-day filtering. PostgreSQL’s generate_series() returns values from an inclusive start through stop using the specified step, making it useful for generating calendar rows and filling gaps in time-series d…

sql-postgres-generate-date-series.sql

-- PostgreSQL Date Series Examples
--
-- generate_series(start, stop, step) creates a row for each value in a range.
-- When working with dates or timestamps, the step is usually an interval.
--
-- Common uses:
--   - Calendar and reporting ranges
--   - Filling missing dates in time-series results
--   - Monthly summaries
--   - Business-day filtering
--   - Date dimension generation


/* --------------------------------------------------------------------------
   1. Generate a daily date series
--------------------------------------------------------------------------- */

SELECT day::date
FROM generate_series(
    DATE '2026-01-01',
    DATE '2026-01-07',
    INTERVAL '1 day'
) AS series(day);


/* --------------------------------------------------------------------------
   2. Generate dates using query parameters
--------------------------------------------------------------------------- */

SELECT day::date
FROM generate_series(
    :start_date::date,
    :end_date::date,
    INTERVAL '1 day'
) AS series(day);


/* --------------------------------------------------------------------------
   3. Generate every day in the current month
--------------------------------------------------------------------------- */

SELECT day::date
FROM generate_series(
    date_trunc('month', CURRENT_DATE),
    date_trunc('month', CURRENT_DATE)
        + INTERVAL '1 month'
        - INTERVAL '1 day',
    INTERVAL '1 day'
) AS series(day);


/* --------------------------------------------------------------------------
   4. Generate every day in the previous month
--------------------------------------------------------------------------- */

SELECT day::date
FROM generate_series(
    date_trunc('month', CURRENT_DATE)
        - INTERVAL '1 month',
    date_trunc('month', CURRENT_DATE)
        - INTERVAL '1 day',
    INTERVAL '1 day'
) AS series(day);


/* --------------------------------------------------------------------------
   5. Generate weekly dates
--------------------------------------------------------------------------- */

SELECT week_start::date
FROM generate_series(
    DATE '2026-01-05',
    DATE '2026-03-30',
    INTERVAL '1 week'
) AS series(week_start);


/* --------------------------------------------------------------------------
   6. Generate monthly dates
--------------------------------------------------------------------------- */

SELECT month_start::date
FROM generate_series(
    DATE '2026-01-01',
    DATE '2026-12-01',
    INTERVAL '1 month'
) AS series(month_start);


/* --------------------------------------------------------------------------
   7. Generate month start and month end dates
--------------------------------------------------------------------------- */

SELECT
    month_start::date,
    (
        month_start
        + INTERVAL '1 month'
        - INTERVAL '1 day'
    )::date AS month_end
FROM generate_series(
    DATE '2026-01-01',
    DATE '2026-12-01',
    INTERVAL '1 month'
) AS series(month_start);


/* --------------------------------------------------------------------------
   8. Generate hourly timestamps
--------------------------------------------------------------------------- */

SELECT hour
FROM generate_series(
    TIMESTAMP '2026-01-01 00:00:00',
    TIMESTAMP '2026-01-01 23:00:00',
    INTERVAL '1 hour'
) AS series(hour);


/* --------------------------------------------------------------------------
   9. Generate 15-minute time intervals
--------------------------------------------------------------------------- */

SELECT interval_start
FROM generate_series(
    TIMESTAMP '2026-01-01 09:00:00',
    TIMESTAMP '2026-01-01 17:00:00',
    INTERVAL '15 minutes'
) AS series(interval_start);


/* --------------------------------------------------------------------------
   10. Exclude weekends
--------------------------------------------------------------------------- */

SELECT day::date
FROM generate_series(
    DATE '2026-01-01',
    DATE '2026-01-31',
    INTERVAL '1 day'
) AS series(day)
WHERE EXTRACT(ISODOW FROM day) BETWEEN 1 AND 5;


/* --------------------------------------------------------------------------
   11. Include useful calendar attributes
--------------------------------------------------------------------------- */

SELECT
    day::date AS calendar_date,
    EXTRACT(YEAR FROM day)::integer AS year,
    EXTRACT(QUARTER FROM day)::integer AS quarter,
    EXTRACT(MONTH FROM day)::integer AS month,
    EXTRACT(WEEK FROM day)::integer AS week,
    EXTRACT(ISODOW FROM day)::integer AS iso_weekday,
    trim(to_char(day, 'Day')) AS weekday_name,
    trim(to_char(day, 'Month')) AS month_name,
    EXTRACT(ISODOW FROM day) IN (6, 7) AS is_weekend
FROM generate_series(
    DATE '2026-01-01',
    DATE '2026-12-31',
    INTERVAL '1 day'
) AS series(day);


/* --------------------------------------------------------------------------
   12. Count records for every date, including dates with no records
--------------------------------------------------------------------------- */

CREATE TABLE orders (
    id         BIGSERIAL PRIMARY KEY,
    created_at TIMESTAMPTZ NOT NULL,
    total      NUMERIC(12, 2) NOT NULL
);

WITH calendar AS (
    SELECT day::date
    FROM generate_series(
        DATE '2026-01-01',
        DATE '2026-01-07',
        INTERVAL '1 day'
    ) AS series(day)
)
SELECT
    calendar.day,
    COUNT(orders.id) AS order_count
FROM calendar
LEFT JOIN orders
    ON orders.created_at >= calendar.day
   AND orders.created_at < calendar.day + INTERVAL '1 day'
GROUP BY calendar.day
ORDER BY calendar.day;


/* --------------------------------------------------------------------------
   13. Daily totals with zero-filled missing dates
--------------------------------------------------------------------------- */

WITH calendar AS (
    SELECT day::date
    FROM generate_series(
        :start_date::date,
        :end_date::date,
        INTERVAL '1 day'
    ) AS series(day)
),
daily_orders AS (
    SELECT
        created_at::date AS order_date,
        COUNT(*) AS order_count,
        SUM(total) AS revenue
    FROM orders
    WHERE created_at >= :start_date::date
      AND created_at < :end_date::date + INTERVAL '1 day'
    GROUP BY created_at::date
)
SELECT
    calendar.day,
    COALESCE(daily_orders.order_count, 0) AS order_count,
    COALESCE(daily_orders.revenue, 0) AS revenue
FROM calendar
LEFT JOIN daily_orders
    ON daily_orders.order_date = calendar.day
ORDER BY calendar.day;


/* --------------------------------------------------------------------------
   14. Monthly totals with missing months filled
--------------------------------------------------------------------------- */

WITH months AS (
    SELECT month_start::date
    FROM generate_series(
        DATE '2026-01-01',
        DATE '2026-12-01',
        INTERVAL '1 month'
    ) AS series(month_start)
),
monthly_orders AS (
    SELECT
        date_trunc('month', created_at)::date AS month_start,
        COUNT(*) AS order_count,
        SUM(total) AS revenue
    FROM orders
    WHERE created_at >= DATE '2026-01-01'
      AND created_at < DATE '2027-01-01'
    GROUP BY date_trunc('month', created_at)::date
)
SELECT
    months.month_start,
    COALESCE(monthly_orders.order_count, 0) AS order_count,
    COALESCE(monthly_orders.revenue, 0) AS revenue
FROM months
LEFT JOIN monthly_orders
    USING (month_start)
ORDER BY months.month_start;


/* --------------------------------------------------------------------------
   15. Generate a reusable calendar table
--------------------------------------------------------------------------- */

CREATE TABLE calendar_dates (
    calendar_date DATE PRIMARY KEY,
    year          INTEGER NOT NULL,
    quarter       INTEGER NOT NULL,
    month         INTEGER NOT NULL,
    week          INTEGER NOT NULL,
    iso_weekday   INTEGER NOT NULL,
    is_weekend    BOOLEAN NOT NULL
);

INSERT INTO calendar_dates (
    calendar_date,
    year,
    quarter,
    month,
    week,
    iso_weekday,
    is_weekend
)
SELECT
    day::date,
    EXTRACT(YEAR FROM day)::integer,
    EXTRACT(QUARTER FROM day)::integer,
    EXTRACT(MONTH FROM day)::integer,
    EXTRACT(WEEK FROM day)::integer,
    EXTRACT(ISODOW FROM day)::integer,
    EXTRACT(ISODOW FROM day) IN (6, 7)
FROM generate_series(
    DATE '2020-01-01',
    DATE '2030-12-31',
    INTERVAL '1 day'
) AS series(day)
ON CONFLICT (calendar_date)
DO NOTHING;


/* --------------------------------------------------------------------------
   Useful notes
--------------------------------------------------------------------------- */

-- The start and stop values are inclusive when the sequence reaches them.

-- Cast generated timestamps to date when only calendar dates are needed:
--
--   day::date

-- Use date_trunc() to align timestamps to calendar boundaries:
--
--   date_trunc('month', CURRENT_DATE)

-- EXTRACT(ISODOW FROM date) returns:
--
--   Monday = 1
--   Sunday = 7

-- When joining TIMESTAMPTZ records to calendar dates, range conditions are
-- often preferable to applying ::date to an indexed timestamp column:
--
--   created_at >= calendar.day
--   AND created_at < calendar.day + INTERVAL '1 day'
--
-- This gives PostgreSQL a better opportunity to use an index on created_at.