Skip to content
Tutorials Center
My learning

Topics

Lesson 27 of 38 ยท Window Functions and Analytics

Time Series: Gaps, Buckets and ASOF Joins

Analyze time series in DuckDB: bucket timestamps, build a date spine to fill gaps, compute moving averages and match events to the latest prior value with ASOF JOIN.

Time series data is events with timestamps: sensor readings, page views, trades, orders. Three jobs come up again and again: bucketing events into hours or days, filling gaps where nothing happened, and matching one stream to the latest value of another. DuckDB has special tools for each, and portable SQL covers the ideas.

The real DuckDB does not run in your browser, so DuckDB-only functions (time_bucket, generate_series on timestamps, ASOF JOIN) are read-only and each is paired with a runnable portable version in SQLite syntax.

Bucketing timestamps

DuckDB has date_trunc and time_bucket:

SQL
SELECT date_trunc('hour', ts) AS hour, count(*) AS events
FROM logs GROUP BY 1 ORDER BY 1;

-- 15-minute buckets
SELECT time_bucket(INTERVAL '15 minutes', ts) AS bucket, avg(temp)
FROM readings GROUP BY 1 ORDER BY 1;

-- 7-day buckets starting on a chosen origin
SELECT time_bucket(INTERVAL '7 days', ts, TIMESTAMP '2026-01-05') AS wk, sum(amount)
FROM orders GROUP BY 1 ORDER BY 1;

date_trunc snaps to calendar units ('day', 'week', 'month', 'quarter', 'year'); time_bucket allows arbitrary widths and origins. The portable equivalent in SQLite uses strftime:

SQL Events per hour and per day (portable)

Press Run (or Ctrl + Enter) to see the result here.

Store timestamps in real TIMESTAMP or TIMESTAMPTZ columns in DuckDB, never as text. Text sorts correctly only in ISO format and cannot use interval math.

Filling gaps

A GROUP BY returns only the buckets that have rows, so a quiet day vanishes from a chart. The fix is a spine: a complete list of buckets that you left-join to the data. DuckDB generates one with generate_series or range:

SQL
SELECT d::DATE AS day, coalesce(sum(o.amount), 0) AS revenue
FROM generate_series(DATE '2026-03-01', DATE '2026-03-07', INTERVAL 1 DAY) AS s(d)
LEFT JOIN orders o ON o.order_date = d::DATE
GROUP BY 1 ORDER BY 1;

In SQLite a recursive CTE builds the spine:

SQL Date spine with a recursive CTE

Press Run (or Ctrl + Enter) to see the result here.

Days with no orders now show 0. For carrying a value forward (a price that stays valid until the next change), use LAST_VALUE(x IGNORE NULLS) OVER (ORDER BY day) in DuckDB. A portable way is a correlated subquery:

SQL Forward-fill with a correlated subquery

Press Run (or Ctrl + Enter) to see the result here.

Moving averages and trailing windows

Smooth noisy data with a frame. In DuckDB a RANGE frame can use time intervals, which stays correct when readings are irregular:

SQL
SELECT ts, temp,
       avg(temp) OVER (
         ORDER BY ts
         RANGE BETWEEN INTERVAL 1 HOUR PRECEDING AND CURRENT ROW
       ) AS avg_last_hour
FROM readings;

Row-based frames (ROWS BETWEEN 5 PRECEDING AND CURRENT ROW) are explained in frames. Use RANGE when time spacing matters and ROWS when each reading counts equally.

ASOF JOIN: the value in force at that moment

Suppose trades happen at exact times, while prices are quoted at other times. For each trade you want the most recent quote at or before the trade. An equality join never matches; an inequality join returns every older quote. ASOF JOIN is built for this:

SQL
SELECT t.ts, t.symbol, t.qty, q.price
FROM trades t
ASOF JOIN quotes q
  ON t.symbol = q.symbol AND t.ts >= q.ts;

For each trade DuckDB finds the quote with the largest q.ts that is <= t.ts for the same symbol. Other operators work too (>= for the nearest earlier row, <= for the nearest later row). ASOF LEFT JOIN keeps trades with no earlier quote (price NULL). It is far faster than a correlated subquery because it merges two sorted inputs.

flowchart LR
    Q1["quote 09:00 = 10.0"] --> T1["trade 09:03 uses 10.0"]
    Q2["quote 09:05 = 10.4"] --> T2["trade 09:07 uses 10.4"]
    Q2 --> T3["trade 09:30 uses 10.4"]

Each trade picks the latest quote not after it.

Runnable stand-in with a correlated subquery:

SQL Stand-in: ASOF match with a subquery

Press Run (or Ctrl + Enter) to see the result here.

The 08:55 trade has no earlier quote, so its price is NULL, just like an ASOF LEFT JOIN.

Sessions and gaps in activity

Detecting a new session after 30 idle minutes is a window problem: flag a row as a session start when the gap from the previous event is large, then take a running sum of the flags.

SQL Sessionization with LAG and a running sum

Press Run (or Ctrl + Enter) to see the result here.

The same SQL works in DuckDB with real timestamps: compare ts - LAG(ts) > INTERVAL 30 MINUTES.

Time zones

Use TIMESTAMPTZ for instants and convert with AT TIME ZONE 'Europe/Berlin' (the ICU extension, bundled in most builds, provides zone names). Bucket in the local zone for business reporting, otherwise "days" start at midnight UTC and shift around daylight-saving changes.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why do charts built from GROUP BY alone often miss quiet periods?

    A group exists only if at least one row falls in it. Join to a complete spine to show zeros.
  2. 2. What does ASOF JOIN match?

    ASOF joins match on the closest value allowed by the inequality, typically the latest timestamp at or before.
  3. 3. When should you prefer a RANGE frame over a ROWS frame for a time series?

    RANGE with intervals follows elapsed time; ROWS counts rows whatever their spacing.
  4. 4. How can sessions be detected with windows?

    The running sum of start-of-session flags yields a session number per user.

Summary

  • Bucket with date_trunc and time_bucket; store real timestamps, not text.
  • Fill gaps by left-joining to a generated spine, then COALESCE or forward-fill.
  • RANGE frames with intervals give true time windows; ROWS counts rows.
  • ASOF JOIN finds the latest prior value; sessions come from LAG plus a running sum.

Next: Extensions: The Plugin System