Bucketing time series and filling gaps with generate_series

Generate the periods with func.generate_series(...).table_valued(...) and left-join the aggregate to them, so buckets with no rows appear with coalesce(value, 0) — a grouped aggregate alone omits empty periods, which breaks charts and makes every lag() reach the wrong neighbour. This guide belongs to window functions and analytical queries.

Quick Answer

GROUP BY returns groups that exist. A day with no orders produces no row, and everything downstream assumes otherwise.

Before — only the days that had data:

import datetime as dt

from sqlalchemy import func, select

from shop.models import Order

stmt = (
    select(
        func.date_trunc("day", Order.placed_at).label("day"),
        func.sum(Order.total_cents).label("revenue_cents"),
    )
    .where(Order.placed_at >= start, Order.placed_at < end)
    .group_by(func.date_trunc("day", Order.placed_at))
    .order_by(func.date_trunc("day", Order.placed_at))
)
# A quiet Sunday is simply absent, so the chart draws a straight line over it
# and lag() compares Monday with Saturday.

After — every day in the range, zero where nothing happened:

import datetime as dt

from sqlalchemy import Date, cast, func, select, text

from shop.models import Order

ZONE = "Europe/Berlin"


def daily_revenue(start: dt.date, end: dt.date):
    day = func.generate_series(
        cast(start, Date),
        cast(end, Date),
        text("interval '1 day'"),
    ).table_valued("day")

    bucket = func.date_trunc("day", func.timezone(ZONE, Order.placed_at))

    daily = (
        select(bucket.label("day"), func.sum(Order.total_cents).label("revenue_cents"))
        .where(Order.placed_at >= start, Order.placed_at < end,
               Order.status != "cancelled")
        .group_by(bucket)
        .cte("daily")
    )

    return (
        select(
            day.c.day,
            func.coalesce(daily.c.revenue_cents, 0).label("revenue_cents"),
        )
        .select_from(day)
        .outerjoin(daily, daily.c.day == day.c.day)
        .order_by(day.c.day)
    )

Three things make it correct: the calendar is the left side of the join, the range is half-open (>= start, < end), and the bucket is truncated in a named time zone rather than whatever the connection happens to be set to.

Execution Context & Async Workflow Integration

generate_series is a set-returning function: given a start, an end and a step, it produces one row per step. SQLAlchemy exposes set-returning functions through table_valued(), which places the call in the FROM clause and names its output column so it can be selected, joined and ordered like any table.

The join is where the density comes from, and its direction is the part that is easy to get wrong. The calendar must be the left side and the aggregate the right, with outerjoin. Reversed, or with an inner join, the empty days are filtered out again and the query is no better than the grouped aggregate alone.

coalesce then decides what an empty bucket means, and the choice is not cosmetic. For a count or a sum, zero is correct: nothing was sold. For an average, a temperature or a gauge, zero is a lie and NULL is the truth — no reading was taken. Reports that fill measurements with zero produce charts that dip to the axis on every gap, which readers interpret as a real drop.

Time zones decide bucket boundaries, and date_trunc on a timestamptz uses the session time zone. That means the same query returns different daily totals depending on the connection's setting, and rows near midnight move between days. Naming the zone in the query — date_trunc('day', timezone('Europe/Berlin', placed_at)) — makes the boundaries explicit and reproducible. Pinning the engine to UTC, as configuring connect_args and server_settings for asyncpg suggests, removes the variation but does not make a UTC day the business day.

Half-open ranges avoid the other double-counting trap. placed_at >= start AND placed_at < end counts each row once when consecutive reports are run; BETWEEN includes both endpoints, so the row at exactly midnight appears in two months. Half-open bounds also let a plain index on placed_at serve the filter, which date_trunc(placed_at) = ... would not — the indexability point from writing hybrid properties that work in Python and SQL.

Under async the whole query is built synchronously and awaited once, returning rows. Nothing lazy-loads, which is why reporting code is some of the least troublesome async SQLAlchemy to write.

Resolving Warnings, Errors & Common Mistakes

The indexability mistake is worth showing, because the two versions look equivalent:

# Not indexable: the expression is applied to every row before comparison.
bad = select(...).where(func.date_trunc("day", Order.placed_at) >= start)

# Indexable: a plain range predicate on the column.
good = select(...).where(Order.placed_at >= start, Order.placed_at < end)

Bucketing belongs in the GROUP BY and the select list; filtering belongs on the raw column. An index on placed_at then serves the range, and the truncation is applied only to the rows that survive it.

For very long ranges at fine granularity, the fix is not a faster query but a different one. A year of five-minute buckets is over a hundred thousand rows, which no chart can display; pre-aggregating into a summary table or a materialised view, and reading from that, is both faster and more honest about what the reader can see. Maintaining such a table in the same transaction as the writes that change it is the data-modifying CTE pattern.

Advanced: Dense Series for Window Functions and Per-Group Gaps

The strongest reason to fill gaps is not presentation — it is that window functions count rows, not periods. lag over a sparse series reaches the previous row, so a missing month makes the comparison silently wrong. Filling first, navigating second, is the order that works:

import datetime as dt

from sqlalchemy import Date, cast, func, select, text

from shop.models import Order

ZONE = "Europe/Berlin"


def monthly_growth(start: dt.date, end: dt.date):
    month = func.generate_series(
        cast(start, Date),
        cast(end, Date),
        text("interval '1 month'"),
    ).table_valued("month")

    bucket = func.date_trunc("month", func.timezone(ZONE, Order.placed_at))
    monthly = (
        select(bucket.label("month"), func.sum(Order.total_cents).label("revenue_cents"))
        .where(Order.placed_at >= start, Order.placed_at < end)
        .group_by(bucket)
        .cte("monthly")
    )

    dense = (
        select(
            month.c.month,
            func.coalesce(monthly.c.revenue_cents, 0).label("revenue_cents"),
        )
        .select_from(month)
        .outerjoin(monthly, monthly.c.month == month.c.month)
        .cte("dense")
    )

    previous = func.lag(dense.c.revenue_cents, 1).over(order_by=dense.c.month)
    return select(
        dense.c.month,
        dense.c.revenue_cents,
        (dense.c.revenue_cents - previous).label("change_cents"),
    ).order_by(dense.c.month)

Because dense has a row for every month, lag now genuinely means "last month" — the correctness point raised in computing period-over-period change with lag and lead.

When the report is per group — revenue per product per day, with every product appearing on every day — the calendar has to be crossed with the groups:

from sqlalchemy import Date, cast, func, select, text

from shop.models import Order, Product

day = func.generate_series(
    cast(start, Date),
    cast(end, Date),
    text("interval '1 day'"),
).table_valued("day")

products = select(Product.id.label("product_id")).where(Product.active.is_(True)).cte("products")

grid = select(day.c.day, products.c.product_id).select_from(day).join(products, text("true")).cte("grid")

stmt = (
    select(
        grid.c.day,
        grid.c.product_id,
        func.coalesce(daily.c.revenue_cents, 0).label("revenue_cents"),
    )
    .select_from(grid)
    .outerjoin(
        daily,
        (daily.c.day == grid.c.day) & (daily.c.product_id == grid.c.product_id),
    )
    .order_by(grid.c.product_id, grid.c.day)
)

The cross join is deliberate and its size is the thing to watch: days multiplied by products. Thirty days and five hundred products is fifteen thousand rows, which is fine; a year and fifty thousand products is not, and needs the product set narrowed — to those with any activity in the range — before crossing.

Calendar Tables and Business Calendars

generate_series produces a sequence of instants. A business report usually needs more than that: whether a day was a trading day, which fiscal quarter it belonged to, how weeks are numbered for this organisation. That information cannot be generated, so it lives in a table.

import datetime as dt

from sqlalchemy import Boolean, Date, Integer, String
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column


class Base(DeclarativeBase):
    pass


class CalendarDay(Base):
    __tablename__ = "calendar_days"

    day: Mapped[dt.date] = mapped_column(Date, primary_key=True)
    is_business_day: Mapped[bool] = mapped_column(Boolean, default=True)
    holiday_name: Mapped[str | None] = mapped_column(String(64))
    fiscal_year: Mapped[int] = mapped_column(Integer)
    fiscal_quarter: Mapped[int] = mapped_column(Integer)
    iso_week: Mapped[int] = mapped_column(Integer)

A calendar table gives three things generate_series cannot. Reports can join to it and filter on business days, so "average daily revenue" divides by the right number. Fiscal periods stop being reimplemented in every query. And because it is an ordinary table, it works on any database, which matters for a codebase that is not PostgreSQL-only.

Populating it is a one-off job per year, and the source for holidays is a business decision rather than a technical one — a library, a finance team's spreadsheet, or a vendor feed. The rows themselves can be generated with generate_series and then annotated:

from sqlalchemy import func, select, text
from sqlalchemy.dialects.postgresql import insert

from shop.models import CalendarDay


async def populate_year(session, year: int, holidays: dict[dt.date, str]) -> None:
    days = [
        {
            "day": day,
            "is_business_day": day.weekday() < 5 and day not in holidays,
            "holiday_name": holidays.get(day),
            "fiscal_year": year if day.month >= 4 else year - 1,
            "fiscal_quarter": ((day.month - 4) % 12) // 3 + 1,
            "iso_week": day.isocalendar().week,
        }
        for day in _dates_in(year)
    ]
    stmt = insert(CalendarDay).values(days).on_conflict_do_nothing(
        index_elements=[CalendarDay.day]
    )
    await session.execute(stmt)
    await session.commit()

Which to use comes down to what the report is for. An operational chart of the last thirty days is well served by generate_series in the query: nothing to maintain, nothing to keep in sync. A financial report that must agree with the finance team's definition of a quarter needs the table, because the definition is data. Many systems have both, and the rule that keeps them from disagreeing is that the table is authoritative wherever it exists — a report should not generate its own calendar for a period the table already describes.

Frequently Asked Questions

Related

• Window Functions and Analytical Queries — The parent guide: partitions, frames and analytical patterns.
• Computing period-over-period change with lag and lead — Why a dense series is a precondition for lag.
• Using non-recursive CTEs to structure reporting queries — Staging a report so each step is inspectable.
• Fixing timezone-aware datetime errors with asyncpg — Storing the instants a bucketed report reads.