Fill missing time windows for device readings¶
CREATE DATABASE cookbook_window;
CREATE TABLE cookbook_window.readings(pump VARCHAR(8), ts DATETIME, value INT);
INSERT INTO cookbook_window.readings VALUES
('p1','2026-01-01 10:00:00',1),('p1','2026-01-01 10:02:00',3);
SELECT pump,_wstart,SUM(value) AS value_sum
FROM cookbook_window.readings
GROUP BY pump INTERVAL(ts,1,minute) GAPFILL(PARTITION) FILL(value,0)
ORDER BY pump,_wstart;
DROP DATABASE cookbook_window;
Expect three one-minute windows at 10:00, 10:01, and 10:02, with value_sum=0 in the middle. Partitioning by pump keeps devices’ timelines separate. See the source gapfill test for other fill methods and a midnight boundary.