QuestDB Time-Series SQL

QuestDB Time-Series SQL: Mastering `SAMPLE BY`, `LATEST ON`, and `ASOF JOIN` (Hands-On Guide)

Meta description (156 chars):
Hands-on QuestDB time-series SQL: use SAMPLE BY for rollups, LATEST ON for last-known values, and ASOF JOIN for nearest time merges—real patterns that ship.

Why this matters (a quick story)

You’ve got billions of events flowing in every day—prices, sensor readings, app metrics. Your team needs rollups, last-known values, and near-time joins to power dashboards and anomaly detectors. QuestDB’s time-series SQL gives you all three with SAMPLE BY, LATEST ON, and ASOF JOIN—no custom pipeline glue or clunky windowing jobs required.

This guide shows how to use each feature correctly and fast, with production caveats mid-level data engineers actually run into.

The setup (minimal schema)

-- Partitioned table with a designated timestamp
CREATE TABLE trades (
  ts        TIMESTAMP,
  symbol    SYMBOL,         -- use SYMBOL for high-cardinality tags
  price     DOUBLE,
  size      INT
) TIMESTAMP(ts)
  PARTITION BY DAY;

TIMESTAMP(ts) elects the designated timestamp—the column QuestDB uses to time-order data and enable time-series features like SAMPLE BY, LATEST ON, and ASOF JOIN. (QuestDB)

`SAMPLE BY`: fast, calendar-aligned rollups

What it does

SAMPLE BY bins rows into fixed time buckets (seconds → years) and computes aggregates—ideal for dashboards and SLOs. Requires a designated timestamp. (QuestDB)

Common pattern

-- Per-minute rollups for today, with sensible gaps handling
SELECT
  ts,                     -- the bucket timestamp
  count()        AS trades,
  avg(price)     AS avg_px
FROM trades
WHERE ts IN today()
SAMPLE BY 1m
FILL(0, PREV);            -- fill count() with 0, avg() with previous value

Tips that save you time:

Fill gaps using FILL(NULL|PREV|LINEAR|<constant>). You can provide one fill per aggregate in SELECT order. (QuestDB)
Align to calendar by default; you can still control time zone and offsets when needed. (QuestDB)
Need rows outside your data’s bounds (pre/post-fill)? Use FROM '...' TO '...' with SAMPLE BY to explicitly shape the output range. (QuestDB)

Small mental model:

raw data  ──●● ●  ●     ●───●   (irregular)
SAMPLE BY 1m → |●|●|●|●|●|●|●|    (regular bins + fill)

`LATEST ON`: last-known values per key

What it does

LATEST ON <timestamp> PARTITION BY <key(s)> returns the most recent row for each series (e.g., per symbol). This needs a designated timestamp (or a subquery that exposes one). (QuestDB)

Common patterns

Latest price per symbol:

SELECT symbol, price, ts
FROM trades
LATEST ON ts PARTITION BY symbol;

Latest in a time slice (note how parentheses change execution order):

-- WHERE first, then latest
SELECT * FROM trades
WHERE ts IN '2025-11-20'
LATEST ON ts PARTITION BY symbol;

-- LATEST first, then filter
(SELECT * FROM trades LATEST ON ts PARTITION BY symbol)
WHERE price > 100;

Execution order matters for result semantics and performance—use parentheses to make it explicit. (QuestDB)

`ASOF JOIN`: nearest-time merges

What it does

ASOF JOIN pairs each left-table row with the latest right-table row at the same or earlier timestamp (optionally within a tolerance). This is how you align trades to quotes, metrics to configs, or events to feature flags. (QuestDB)

Trades to quotes with a 2-second window:

SELECT
  t.ts, t.symbol, t.price,
  q.bid, q.ask
FROM trades t
ASOF JOIN quotes q ON (symbol)
TOLERANCE 2s;

Important details:

By default, QuestDB uses each table’s designated timestamp for the join. (QuestDB)
If you must join on a different time column, sort a subquery (ORDER BY other_ts ASC) so that column becomes the subquery’s effective timestamp for the join. The engine will use it automatically. (QuestDB)
Use TOLERANCE to bound the look-back window—it improves correctness and performance. (QuestDB)

Visual intuition (per key)

left (trades):   -----t1-----------t2----------t3----
right (quotes):  ---qA------qB-------------qC--------
ASOF result:     t1→qA     t2→qB           t3→qC

When to use which? (cheat sheet)

Clause	Solves for	Typical SELECT pattern	Gotchas / notes
`SAMPLE BY`	Rollups & resampling	`... SAMPLE BY 1m FILL(0, PREV)`	Needs designated timestamp; fill rules differ for keyed vs non-keyed queries; default aligns to calendar. (QuestDB)
`LATEST ON`	Last-known value per series/key	`... LATEST ON ts PARTITION BY symbol`	Execution order with `WHERE` changes semantics; multi-column partitions may scan more. (QuestDB)
`ASOF JOIN`	Nearest-time merge across tables	`... ASOF JOIN b ON (key) TOLERANCE 2s`	Uses designated timestamp by default; for other ts, `ORDER BY` in a subquery; add `TOLERANCE`. (QuestDB)

Real example end-to-end

Goal: build a per-minute trade volume dashboard, overlay last price per symbol, and enrich with near-time quote spreads.

-- 1) Per-minute rollups (count + avg price)
WITH minute_rollups AS (
  SELECT ts, count() AS trades, avg(price) AS avg_px
  FROM trades
  WHERE ts IN today()
  SAMPLE BY 1m
  FILL(0, PREV)
),

-- 2) Latest price per symbol (time slice)
latest_px AS (
  (SELECT symbol, price, ts
   FROM trades
   WHERE ts IN today()
   LATEST ON ts PARTITION BY symbol)
),

-- 3) Enrich rollups with quotes near each minute mark
rollups_with_spread AS (
  SELECT
    m.ts,
    m.trades,
    m.avg_px,
    q.bid,
    q.ask,
    (q.ask - q.bid) AS spread
  FROM minute_rollups m
  ASOF JOIN quotes q TOLERANCE 2s ON ()   -- join by time only; or ON (symbol)
)

SELECT *
FROM rollups_with_spread
ORDER BY ts;

This pattern avoids custom stream jobs for last-value lookups and near-time alignment—pure SQL.

Best practices and common pitfalls

Schema & partitions

Always set a designated timestamp; it unlocks time-series features and optimal scans. (QuestDB)
Choose partitions that match retention and query windows (e.g., PARTITION BY DAY). You can add TTLs to drop old partitions automatically. (QuestDB)

SAMPLE BY

Use FILL(0, PREV, ...) to keep charts continuous; prefer constants for additive metrics (count()), PREV or LINEAR for ratios and averages. (QuestDB)
If you need rows before/after data exists (pre/post-fill ranges), use FROM ... TO .... (QuestDB)

LATEST ON

For unordered data or tables without a designated timestamp, wrap with a subquery; otherwise the engine can’t derive “latest.” (QuestDB)
Be explicit about execution order with parentheses when mixing WHERE and LATEST ON. (QuestDB)

ASOF JOIN

Start with the default: rely on each table’s designated timestamp. If you must use another column, ORDER BY <col> ASC in a subquery so QuestDB picks it up as the subquery timestamp. (QuestDB)
Add a TOLERANCE window to avoid accidental joins to stale rows and to speed up lookups. (QuestDB)

Key references (official)

SAMPLE BY reference & fill behavior. (QuestDB)
LATEST ON reference (syntax, execution order). (QuestDB)
ASOF JOIN reference (semantics, tolerance). (QuestDB)
Designated timestamp concept. (QuestDB)
CREATE TABLE (partitions, TTL, WAL default). (QuestDB)

Internal link ideas (official pages to link from this article)

QuestDB SAMPLE BY SQL reference – full syntax, FILL, alignment, FROM...TO. (QuestDB)
LATEST ON reference – single vs multi-key, execution order examples. (QuestDB)
ASOF JOIN reference – default timestamp usage, TOLERANCE. (QuestDB)
Designated timestamp concept – why it matters, properties. (QuestDB)
JOIN overview – supported join types (ASOF, LT, SPLICE). (QuestDB)
CREATE TABLE – partitions, TTL, dedup, WAL defaults. (QuestDB)
Time-series optimizations – engine behaviors worth knowing. (QuestDB)
Materialized views – precompute rollups when queries get heavy. (QuestDB)

Summary & call-to-action

Use SAMPLE BY for rollups and resampling (remember FILL).
Use LATEST ON for last-known per-key values—watch execution order.
Use ASOF JOIN to align data across tables in time—add TOLERANCE.

Spin up a small dataset, run the snippets above, and then check the linked docs to deepen your understanding. Next step: add TTL and materialized views to keep query latency flat as data grows.

Image prompt (for your designer/AI tool)

“A clean, modern data architecture diagram showing QuestDB tables with a designated timestamp, a SAMPLE BY 1m rollup, a LATEST ON selection per symbol, and an ASOF JOIN into a quotes table — minimalistic, high contrast, 3D isometric style.”

Data/ML Engineer Blog