Time Series Aggregation in SQL: Bucketing Data and Filling Gaps

1. Why time series aggregation is a problem of its own

Classic time series aggregation groups events, orders or measurements over a time column to produce daily, weekly or monthly values. That sounds like a plain GROUP BY, but it differs from ordinary aggregation in one crucial way: the time axis has a fixed, expected structure that the raw data often does not contain at all. A reporting dashboard expects one row per day of a month, even if not a single order came in on some of those days.

This is exactly where the structural problem appears: a plain GROUP BY over a date column only produces rows for periods where data actually exists. Missing days, weeks or months disappear from the result entirely, instead of showing up with a value of zero. For a line chart in a report that means misleadingly interpolated gaps, and for a year over year comparison it simply means missing comparison points.

This article systematically shows how to implement clean time series aggregation in SQL: from building fixed time buckets to filling missing periods, comparison periods and performance considerations for large time series tables. All examples work with small adjustments in PostgreSQL, MySQL and SQL Server alike.

2. Bucketing data with DATE_TRUNC and DATE_FORMAT

The first step of any time series aggregation is forming time buckets from a timestamp. In PostgreSQL, DATE_TRUNC handles this by rounding a timestamp down to the desired precision, such as the start of the day, the start of the week or the first of the month. In MySQL the same result is usually achieved with DATE_FORMAT combined with STR_TO_DATE, since MySQL offers no direct equivalent of DATE_TRUNC. SQL Server takes over the same task with DATETRUNC from version 2022 onward.

What matters for correct time series aggregation is that every timestamp within a bucket actually receives the same rounded value, regardless of the time of day or time zone of the individual event. Ignoring time zones risks that an event at 23:50 local time lands in the wrong daily bucket as soon as the database calculates internally in UTC. In internationally used systems, time zone conversion should therefore happen before bucket formation, not after.

-- Time series aggregation: build daily buckets in PostgreSQL
SELECT
    date_trunc('day', created_at) AS day_bucket,
    COUNT(*) AS order_count,
    SUM(total_amount) AS revenue
FROM orders
WHERE created_at >= '2026-06-01'
GROUP BY date_trunc('day', created_at)
ORDER BY day_bucket;

-- Equivalent bucket formation in MySQL 8+
SELECT
    DATE(created_at) AS day_bucket,
    COUNT(*) AS order_count,
    SUM(total_amount) AS revenue
FROM orders
WHERE created_at >= '2026-06-01'
GROUP BY DATE(created_at)
ORDER BY day_bucket;

3. The gap problem: why GROUP BY hides missing days

As soon as the first report with buckets is ready, the real problem of time series aggregation becomes visible: on days without orders, not a single row exists in the source table, so GROUP BY produces no output row for that day either. A thirty day report can suddenly return only twenty two rows without any error occurring. From the database's point of view this is correct behavior, but from the reporting point of view it is almost always wrong.

The consequences range from harmless to critical. A line chart simply draws a straight line over the missing points, creating the impression of continuous revenue even though nothing happened that day. A metric like the average daily order count over the month gets skewed, because days with zero orders effectively drop out of the average calculation instead of contributing a zero. For reliable reporting it is therefore mandatory to have every expected period explicitly present in the result, even with a value of zero.

4. Calendar tables: the robust solution for gapless reports

The most robust solution in practice for gapless time series aggregation is a dedicated calendar table. This is simply a table with one row per calendar day over a sufficiently long period, often pre populated decades in advance. Additional columns for weekday, calendar week, month, quarter and a holiday flag turn the table into the central reference for every time based analysis in the company, not just for a single report.

The actual trick lies in a LEFT JOIN from the calendar table onto the aggregated raw data, rather than the other way around. Because the calendar table sits on the left, every calendar row survives in the result even when the right side offers no matching row for that day. COALESCE turns the resulting NULL values into clean zeros. This technique is database agnostic, works identically in PostgreSQL, MySQL and SQL Server, and combines easily with further dimensions such as stores or product categories via a CROSS JOIN.

-- Time series aggregation with a calendar table: no gaps in the output
SELECT
    cal.calendar_date,
    COALESCE(o.order_count, 0) AS order_count,
    COALESCE(o.revenue, 0) AS revenue
FROM calendar cal
LEFT JOIN (
    SELECT
        DATE(created_at) AS day_bucket,
        COUNT(*) AS order_count,
        SUM(total_amount) AS revenue
    FROM orders
    GROUP BY DATE(created_at)
) o ON o.day_bucket = cal.calendar_date
WHERE cal.calendar_date BETWEEN '2026-06-01' AND '2026-06-30'
ORDER BY cal.calendar_date;

5. generate_series and recursive CTEs as an alternative

Anyone who does not want to maintain a permanent calendar table can generate a time series ad hoc instead. PostgreSQL offers the function generate_series for this, which produces timestamps between a start and an end point at a fixed interval, without any helper table. The result can be joined to the aggregated raw data with a LEFT JOIN exactly like a calendar table, with the advantage that the period can be passed flexibly as a query parameter instead of being maintained beforehand in a table.

In database systems without generate_series, such as MySQL or SQL Server, a recursive common table expression takes over the same task. Starting from a start date, each recursive step adds one day until the end date is reached. This technique is somewhat more cumbersome to write, but functionally delivers the same result as generate_series, making it the portable variant for systems without a native series function. For very long periods a calendar table is still preferable, because recursive CTEs need noticeably more time across thousands of iterations.

-- PostgreSQL: ad hoc time series with generate_series, no helper table needed
SELECT
    d.day_bucket,
    COALESCE(o.order_count, 0) AS order_count
FROM generate_series(
    '2026-06-01'::date, '2026-06-30'::date, '1 day'
) AS d(day_bucket)
LEFT JOIN (
    SELECT DATE(created_at) AS day_bucket, COUNT(*) AS order_count
    FROM orders GROUP BY DATE(created_at)
) o ON o.day_bucket = d.day_bucket
ORDER BY d.day_bucket;

-- Portable alternative: recursive CTE for databases without generate_series
WITH RECURSIVE date_range AS (
    SELECT CAST('2026-06-01' AS DATE) AS day_bucket
    UNION ALL
    SELECT DATE_ADD(day_bucket, INTERVAL 1 DAY)
    FROM date_range
    WHERE day_bucket < '2026-06-30'
)
SELECT day_bucket FROM date_range;

6. Multiple granularities: day, week, month, quarter

Reporting dashboards rarely need only a single granularity. The same time series aggregation often needs to be available at daily, weekly, monthly and quarterly level at the same time, without writing a separate query for every level. The elegant way is to use DATE_TRUNC once with the target granularity as a parameter and reuse the same base query for every level, instead of duplicating the aggregation code for each one.

A common approach for dashboards with switchable granularity is to pre aggregate the raw data at daily level first and derive the coarser levels from this already condensed intermediate layer with a further GROUP BY. This drastically reduces the amount of data that has to be regrouped for weekly, monthly and quarterly analyses, and it makes switching between granularities in the frontend noticeably faster, because the full raw data table does not need to be scanned every time.

-- Multiple granularities from one pre-aggregated daily layer
WITH daily AS (
    SELECT DATE(created_at) AS day_bucket, SUM(total_amount) AS revenue
    FROM orders
    GROUP BY DATE(created_at)
)
SELECT
    date_trunc('month', day_bucket) AS month_bucket,
    SUM(revenue) AS monthly_revenue,
    date_trunc('quarter', day_bucket) AS quarter_bucket
FROM daily
GROUP BY date_trunc('month', day_bucket), date_trunc('quarter', day_bucket)
ORDER BY month_bucket;

7. Comparison periods with LAG: previous year and previous week

A central part of reporting is comparing against previous periods. Instead of writing two separate queries for the current and the previous period and merging the results in the application layer, the window function LAG solves this task directly within a single time series aggregation. LAG accesses the value of a previous row within the same, time ordered result set, without requiring a self join.

For a year over year comparison, a LAG with an offset of twelve on monthly aggregated data is enough, and for a week over week comparison an offset of one on weekly aggregated data suffices. From the current and the previous value, the percentage change can be computed directly in the same query, which is practically always needed for growth metrics in dashboards. This technique only works reliably once the time series has been gaplessly filled as described in sections four and five, otherwise a missing period shifts every LAG offset.

8. Performance: indexing and pre-aggregation

Large time series tables with millions of events place their own performance demands on time series aggregation. An index on the timestamp column is the basic requirement, but it often is not enough for function based filtering such as WHERE DATE(created_at) = ..., because many databases can no longer use a plain index once a function is applied to the indexed column. A function based index on exactly that expression, or alternatively a range filter with BETWEEN on the raw timestamp, reliably works around this problem.

For very large tables and recurring dashboards, genuine pre aggregation pays off as well: instead of grouping over millions of raw rows on every page load, a separate, regularly refreshed rollup with daily values is maintained, for example as a materialized view or as a table populated by a cron job. Dashboards then only read from this already condensed rollup, which can reduce response time from seconds to milliseconds, without changing the underlying time series aggregation logically.

9. Time series aggregation approaches compared

The techniques presented solve the same underlying problem in different ways, with clear trade offs between maintenance effort, flexibility and performance. The following overview places the four most important approaches to time series aggregation in their typical area of use.

Anyone who plans time series aggregation with a calendar table and explicit gap filling from the start saves later, costly corrections to dashboards that appear correct while actually showing skewed average values. This investment pays off especially for metrics compared over longer periods, such as annual revenue or customer growth.

10. Summary

Clean time series aggregation differs from ordinary aggregation because the time axis has a fixed, expected structure that is often missing from the raw data. DATE_TRUNC builds the buckets, a calendar table or generate_series fills missing periods with a zero value, and LAG computes comparison periods like previous year or previous week directly in the same query. Combining these building blocks yields reports that actually show every expected period instead of silently hiding gaps.

For large time series tables, the performance dimension adds another layer: function based indexes and a pre computed pre aggregation reduce dashboard response time significantly, without affecting the correctness of the time series aggregation. The techniques presented are mostly database agnostic and can be transferred from PostgreSQL to MySQL or SQL Server with little effort.

11. FAQ: Time Series Aggregation in SQL