Time-Series Queries

Daily Buckets

Group Events by Day

Time-series queries often bucket timestamped rows into daily totals.

Program

Play the script to choose a service and group its request counts by day.

daily_bucket_totals.sql 
CREATE TABLE metrics (recorded_at TEXT, service TEXT, requests INTEGER);
INSERT INTO metrics VALUES ('2026-03-01 09:00', 'api', 120), ('2026-03-01 10:00', 'web', 80), ('2026-03-02 09:00', 'api', 150), ('2026-03-02 10:00', 'web', 95), ('2026-03-02 11:00', 'api', 40);
WITH params(wanted_service) AS (VALUES ()) SELECT substr(recorded_at, 1, 10) AS day, SUM(requests) AS total_requests FROM metrics WHERE service = (SELECT wanted_service FROM params) GROUP BY substr(recorded_at, 1, 10) ORDER BY day;

CREATE TABLE metrics (recorded_at TEXT, service TEXT, requests INTEGER);
INSERT INTO metrics VALUES ('2026-03-01 09:00', 'api', 120), ('2026-03-01 10:00', 'web', 80), ('2026-03-02 09:00', 'api', 150), ('2026-03-02 10:00', 'web', 95), ('2026-03-02 11:00', 'api', 40);
WITH params(wanted_service) AS (VALUES ()) SELECT substr(recorded_at, 1, 10) AS day, SUM(requests) AS total_requests FROM metrics WHERE service = (SELECT wanted_service FROM params) GROUP BY substr(recorded_at, 1, 10) ORDER BY day;
bucket `substr(recorded_at, 1, 10)` keeps the date part of an ISO timestamp.
GROUP BY `GROUP BY` collects all matching events for the same day.
time-series filter The selector changes which service is bucketed without changing the query shape.