History range queries are off by the UTC offset (timestamps compared as local-time text) #11
Loading…
Reference in a new issue
No description provided.
Delete branch "%!s()"
Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?
Problem
The history range queries compare timestamps as text, and the timestamps are stored in local time. The range bounds come from the API in whatever offset the caller sends, usually UTC (
…Z). The range is then off by the UTC offset (1 h in winter, 2 h in summer).The modernc SQLite driver stores
time.Timeast.String(), e.g.2026-10-28 07:58:08.038 +0100 CET m=+35740.25. The repositories queryWHERE timestamp >= ? AND timestamp <= ?with the boundtime.TimefromparseTimeRangeParams. For…Zinput that is2026-10-28 07:03:18 +0000 UTC, which compares as text against the local-time strings.Seen
/api/history/heating?start=…&end=2026-10-28T07:03:18Zmisses the switch-off at 07:58:08 CET (06:58:08 UTC), which is inside the range.+01:00) get the right rows, which is how the release watcher works around it.Affects all range queries: temperature history, heating history, events, anticipation samples and coast records. It also hits the history graph, the CSV/JSON exports and the analysis in #8. Around the DST change, local-time text also stops sorting correctly for an hour.
ORDER BY timestamphas the same problem; the restore code already orders byidfor that reason.Fix options
time.Localin the repositories before querying. The text then compares like-for-like, except in the DST hour._time_formatDSN parameter can set the format. Existing rows (back to 2019) need a migration to rewrite them, parsing thet.String()text and dropping them=+…monotonic suffix.