Menu

One day is not 24 hours: date arithmetic that breaks quietly, measured on SQLite and D1
πŸ“°

One day is not 24 hours: date arithmetic that breaks quietly, measured on SQLite and D1

DEV Community: webdevΒ·MapToSiteΒ·1 day ago
#Xbjhorvg
#devtowebdev#devto#dev#date#asia
Reading 0:00
15s threshold

A traffic table I look after has one timestamp column and one job: answer "how many people came in the last 24 hours". I rewrote that query three times last week, and the three versions disagreed β€” not by a rounding error, but by a whole day.

The table was fine. The data was fine. The column is TEXT holding UTC timestamps, written by three code paths that each picked a different way to write the same instant. Here is what a single UTC day range does to that table, counted over 6,480 rows per format:

how the timestamp was written rows counted in >= '2026-11-15 00:00:00' AND <= '2026-11-15 23:59:59'

2026-11-15 00:40:51 (space, datetime('now') style) 4,320

2026-11-15T00:40:51Z (ISO-8601 with T and Z ) 0

2026-11-15T09:40:51+09:00 (ISO-8601 with an offset) 0

Same instants, three formats, one query, and two of them report that nothing happened.…

Continue reading β€” create a free account

Join HashtagPLUS to read full articles, follow hashtags, vote, and join the conversation.