Daily Buckets - SQL Programming

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.
                            wanted_service
                            wanted_service = 'api' (default)wanted_service = 'web'
                        

                            daily_bucket_totals.sql
                        
Replay: real traced execution (multi-file project)

                            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 ('api')) 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 ('web')) 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;

                        
1tables ← 1 row1CREATE TABLE metrics (recorded_at TEXT, service TEXT, requests INTEGER);2INSERT 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);⋯values this step1 rowtables
2metrics ← 5 rows1CREATE TABLE metrics (recorded_at TEXT, service TEXT, requests INTEGER);2INSERT 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);3WITH params(wanted_service) AS (VALUES ('api')) 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;values this step5 rowsmetrics
3result ← 2 rows⋯2INSERT 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);3WITH params(wanted_service) AS (VALUES ('api')) 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;values this step2 rowsresult
1tables ← 1 row1CREATE TABLE metrics (recorded_at TEXT, service TEXT, requests INTEGER);2INSERT 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);⋯values this step1 rowtables
2metrics ← 5 rows1CREATE TABLE metrics (recorded_at TEXT, service TEXT, requests INTEGER);2INSERT 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);3WITH params(wanted_service) AS (VALUES ('web')) 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;values this step5 rowsmetrics
3result ← 2 rows⋯2INSERT 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);3WITH params(wanted_service) AS (VALUES ('web')) 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;values this step2 rowsresult
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.