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.β¦