Query dated measurements ​

Store a measurement's time, identity and value in declared columns. Choose Date for a calendar day or a documented timestamp type when time of day matters. State the timezone convention before comparing or aggregating timestamps.

CREATE TABLE daily_measurements(day Date, sensor_id INTEGER, value Float64);
INSERT INTO daily_measurements VALUES
    ('2025-01-01', 1, 10.0), ('2025-01-01', 1, 14.0),
    ('2025-01-02', 1, 12.0);

SELECT day, sensor_id, count(*) AS samples, avg(value) AS mean_value
FROM daily_measurements
GROUP BY day, sensor_id
ORDER BY day, sensor_id;

This query groups available rows. A day with no measurement does not appear automatically. Use a generated calendar or another date relation when a report must distinguish missing observations from a measured zero.

Use half-open ranges for adjacent reporting periods so their shared boundary is not counted twice. A targeted index can help repeated time-and-identity lookups; inspect the actual plan before keeping it. Retention is an explicit DELETE task, not automatic TTL processing.

See Date, DateTime, series generation and retention.