Time-series queries

Windows, rollups, multi-dimensional splits, gap-filling, feeds, and retention recipes.

Version
Latest
v0.1.0 · latest 1 min read
On this page
  1. Windows
  2. Rollups
  3. Gap-filling without holes
  4. Feeds (keyset)
  5. Retention (manual)
  6. Bulk-seed a time series (generate_series + dynamic intervals)

Windows#

sqlsource
SELECT * FROM events WHERE ts >= now() - INTERVAL '1 hour' AND ts < now() ORDER BY ts DESC LIMIT
  100;
SELECT * FROM events WHERE ts BETWEEN '2026-01-01' AND '2026-02-01' ORDER BY ts;

Rollups#

sqlsource
SELECT date_trunc('hour', ts) h, count(*), avg((payload->>'v')::numeric) FROM events
WHERE ts >= now() - INTERVAL '7 days' GROUP BY date_trunc('hour', ts) ORDER BY 1;
SELECT date_trunc('day', ts) d, kind, count(*) FROM events GROUP BY date_trunc('day', ts), kind
  ORDER BY 1, 2;
SELECT count(*) FILTER (WHERE payload @> '{"ok": true}'),
       count(*) FILTER (WHERE payload @> '{"ok": false}') FROM events
WHERE ts >= now() - INTERVAL '1 day';

Gap-filling without holes#

generate_series takes integers only, so build the hour grid arithmetically (verified pattern):

sqlsource
WITH hours AS (
  SELECT date_trunc('hour', now()) - (g || ' hours')::interval AS h
  FROM generate_series(0, 23) AS g
)
SELECT hours.h, count(e.id) FROM hours
LEFT JOIN events e ON date_trunc('hour', e.ts) = hours.h
GROUP BY hours.h ORDER BY hours.h;

Feeds (keyset)#

sqlsource
SELECT * FROM events ORDER BY ts DESC, id DESC LIMIT 50;
-- next page: repeat with the last row's values (row-wise tuple comparison is unsupported)
SELECT * FROM events WHERE ts < '2026-01-02T00:00Z' ORDER BY ts DESC, id DESC LIMIT 50;

Retention (manual)#

sqlsource
DELETE FROM events WHERE ts < now() - INTERVAL '90 days' RETURNING *;
-- or partition by month table + DROP/TRUNCATE old partitions

Bulk-seed a time series (generate_series + dynamic intervals)#

sqlsource
INSERT INTO events (id, ts, kind, payload)
SELECT g, '2026-07-01 08:00:00'::timestamp + (g || ' minutes')::interval,
  CASE WHEN g % 5 = 0 THEN 'error' ELSE 'click' END,
  json_build_object('seq', g)::jsonb
FROM generate_series(1, 10000) AS g;

(g || ' minutes')::interval builds per-row offsets from string concatenation — the standard seeding idiom (see e-commerce bulk load).

Related: Temporal · Performance

Was this page helpful?