Time Bucketing and SCD: Two Traps in Handling Time with SQL

Following the previous post‘s “ranges”, this one covers two traps time sets in SQL — both rooted in the same fact: time is continuous, but your data is discrete events. In between there are inevitably gaps where “nothing happened”, and changes where “the value changed but you didn’t record it”. SQL won’t handle these for you automatically; you have to act.

Trap one: time bucketing drops empty buckets

For “per day / per hour” aggregation, the standard approach is date_trunc('day', ts) to put timestamps into buckets and then GROUP BY. But a quiet trap hides here: GROUP BY only produces buckets that “have data” — a day with no events at all simply has no row, and your time series has a hole in it:

The root of the problem is “absence being silently treated as non-existence”. The fix is don’t let the data decide which buckets exist; build the complete time axis yourself: generate every day with generate_series, LEFT JOIN the data onto it, and fill the unmatched with COALESCE 0:

-- ❌ days with no orders never appear
SELECT date_trunc('day', created_at) AS day, COUNT(*) AS orders
FROM orders
GROUP BY 1 ORDER BY 1;

-- ✅ build the full date axis first, then fill the holes
SELECT d.day, COALESCE(COUNT(o.id), 0) AS orders
FROM generate_series(DATE '2026-07-01', DATE '2026-07-04', INTERVAL '1 day') AS d(day)
LEFT JOIN orders o ON date_trunc('day', o.created_at) = d.day
GROUP BY d.day ORDER BY d.day;

Trap two: dimensions change, and you need to remember history

The second trap: dimensions change slowly — customers move, products get recategorised, sales reps change region. If you overwrite the old value directly, the history is gone forever: which tax region was last year’s order computed under at the time? Whose name was that deal under? The data warehouse’s standard answer to this is SCD (Slowly Changing Dimension), and the most common form is Type 2: don’t overwrite; add a row for every change, marked with a validity range:

With this history table, “go back to a point in time and see what it looked like” is just a range query:

-- the city on record for customer 1 as of 2026-02-01 (answer: Taipei)
SELECT city FROM customer_history
WHERE customer_id = 1
  AND valid_from <= DATE '2026-02-01'
  AND DATE '2026-02-01' < valid_to;

By contrast, Type 1 is simply UPDATEing over the old value — less work, but the history is gone for good. Whether to keep history is a trade-off to settle when you model: dimensions that will be used for historical analysis, audit, or “what was it at the time” should almost all be Type 2.

Reflections

”No data” is data too

The dropped-empty-bucket trap is, at heart, “absence silently treated as non-existence”. But in a time series, “that day was 0” and “that day has no row” mean completely different things — one is explicit information (there really were no orders that day), the other is a hole in the data (you never produced that cell). Plotted, or fed to a downstream model, the difference amplifies into a wrong reading of the trend. It’s the same ailment as the NULL post: “runs, but quietly missing something”. So the first thing I do with any time series now is decide “how are empty periods represented”, and actively fill the axis with generate_series, rather than letting GROUP BY hide a 0 as “doesn’t exist”.

SCD Type 2 is “version control” for data

Once Type 2 clicked, I saw it as essentially git for dimension data — every change stores a timestamped version instead of overwriting. The only difference is that you store a “validity range” rather than a commit. That lens makes it easier to decide between Type 1 and Type 2: will you ever want to git blame this column? If yes (the price at the time, the owner at the time, the category at the time), Type 2; if no, and only the current value matters (a user’s display nickname, say), Type 1 overwriting is fine. Overwriting saves effort; the price is that you can never go back — a trade-off that exists in any system that stores state, not just data warehouses.

Time makes SQL hard because it has “ranges that don’t exist”

Looking back, these two traps share a root, and they’re family with the previous post‘s gaps and islands: time is continuous, data is discrete — there are always “gaps where nothing happened” and “changes where the value switched but nothing was recorded”. SQL won’t fill these in for you; you have to actively build the complete time axis (generate_series) and actively record the ranges of change (valid_from/valid_to). Recognising that time has gaps and boundaries you must fill yourself is the first lesson in handling any time series; and what these posts (dedupe, contiguous ranges, time) keep saying is really one thing — real-world data is messy and discontinuous, and tidying it into a clean, analysable shape is the daily craft of data engineering.