On this page
Windows#
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#
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):
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)#
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)#
DELETE FROM events WHERE ts < now() - INTERVAL '90 days' RETURNING *;
-- or partition by month table + DROP/TRUNCATE old partitionsBulk-seed a time series (generate_series + dynamic intervals)#
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? Thanks — noted locally, nothing is sent anywhere.