My "Last Hour" Query Returned 1,252 Rows. The Real Answer Was 68.

A developer behind the independent project AI Change Watch found that a SQLite freshness query returned 1,252 crawl runs for the last hour when the true count was 68, because SQLite's datetime() emits a space at position 11 while stored ISO 8601 timestamps use a 'T' (0x54 vs 0x20), making the comparison a byte-wise text comparison that treats any same-day row as newer than the cutoff. The shipped application code was unaffected since it builds bounds in JavaScript with toISOString(), leaving the bug confined to hand-written operational queries; the fix is to normalize the cutoff with strftime('%Y-%m-%dT%H:%M:%SZ', 'now', '-1 hour').

I run AI Change Watch https://aichangewatch.com/?src=devto , a small independent project that crawls what 15 AI vendors publish about their own models — deprecation tables, lifecycle pages, pricing and SDK releases — and records every time one of them changes. Every so often I check whether the crawler is still alive by counting recent runs. The query is the obvious one: SELECT COUNT FROM crawl runs WHERE started at datetime 'now', '-1 hour' ; It returned 1,252 . The true number of runs in that hour was 68 . No error. No warning. A plausible-looking integer, roughly eighteen times too large, from a query that reads correctly in review. crawl runs.started at is written by application code, as ISO 8601: 2026-08-24T17:40:41.965Z SQLite's datetime returns its own format, which uses a space instead of T and has no fractional part or zone suffix: sqlite SELECT datetime 'now', '-1 hour' ; 2026-08-24 16:54:52 Both are strings. SQLite has no dedicated date type — dates are TEXT, REAL or INTEGER by convention — so here is a text comparison , byte by byte. And that is where it goes wrong, at exactly one character: | position 11 | value | byte | |---|---|---| | stored | T | 0x54 | | cutoff | ' ' | 0x20 | T sorts above a space. So for every row whose date part is the same day as the cutoff , the comparison stops at position 11, finds 0x54 0x20 , and answers greater — no matter what time it actually is. A run from 00:03 that morning is "in the last hour." The fix is to make the cutoff the same shape as the column: -- wrong: 1252 rows WHERE started at datetime 'now', '-1 hour' -- right: 68 rows WHERE started at strftime '%Y-%m-%dT%H:%M:%SZ', 'now', '-1 hour' Because both of its failure modes are comfortable. For freshness checks it fails loud. The number comes out too big, which means a stalled crawler still looks busy. This is the dangerous direction — it is precisely the check whose whole job is to tell you something stopped, and it is biased toward saying everything is fine. For windowed audits it fails safe. -1 day or -7 day pulls in the entire boundary day, so a review window is wider than you stated, never narrower. Nothing is missed; you just quietly reviewed more than you meant to. Nobody notices being handed extra. Neither shows up in the output. You do not get a type error, a coercion warning, or an empty result that makes you look twice. You get rows, and they are real rows, and they are formatted like the ones you wanted. Here is the part I found most interesting once I went looking. My shipped code was never affected. Nothing under src/ or web/ calls SQLite's datetime 'now' or julianday 'now' at all. Every bound in the application is built in JavaScript: js const since = new Date Date.now - 86 400 000 .toISOString ; // '2026-08-23T17:40:41.965Z' — same shape as the column Which emits the identical T / Z form, so the comparison is like-for-like everywhere it ships. The bug lived entirely in hand-written operational queries — the ones I type into a console to answer a question right now. That is the least-examined code in any project: Several "runs in the last hour" figures I had quoted before finding this were inflated by it. The application was healthy the whole time; the instrument was wrong. Two escape hatches, in the order I'd reach for them. Normalise the cutoff, not the column. Rewriting stored data is a migration; rewriting a cutoff is a line. strftime above is the clean version, but if you already have datetime calls scattered through a script, patching them in place also works: WHERE started at replace datetime 'now', '-1 hour' , ' ', 'T' || 'Z' I prefer strftime — it states the format it produces instead of repairing one — but this is fine when you are editing twenty ad-hoc queries and want a mechanical change. Or stop storing dates as text. SQLite has no date type; the documentation offers three conventions — ISO-8601 TEXT, Julian day as REAL, and Unix epoch as INTEGER — and the whole class of bug in this post only exists in the first one. Integers compare as numbers, so there is no format to disagree about: WHERE started at ms unixepoch 'now', '-1 hour' 1000 The trade is readability. 1787143241965 in a console tells you nothing, and every query you write by hand now needs a conversion to be legible. For a table I mostly read by eye I kept the text column and fixed the cutoffs. For one I only ever compared, I would not. Worth knowing which trade you made, rather than discovering it at position 11 of a string. Grep for the mixed forms. If both of these return hits in the same project, you have the ingredients: rg "datetime\ 'now'|julianday\ 'now'" SQL-side clocks rg "toISOString\ \ " JS-side timestamps The bug is not either one. It's a comparison with one of each on opposite sides. Cross-check any window with a grouping. This is the cheap, general test, and it needs no knowledge of the storage format: SELECT substr started at, 1, 13 AS hour, COUNT FROM crawl runs GROUP BY hour ORDER BY hour DESC LIMIT 5; If the hourly buckets don't sum to what your windowed query claimed, the comparison is the reason. That is how I found the 68. Look at your columns before you compare them. One SELECT started at FROM crawl runs LIMIT 1 would have shown me the T at any point in the preceding months. The general form of this, which is not really about SQLite: a comparison between two values that were produced by different systems is a format assumption, whether or not you wrote it down. Text Found 2026-08-24, fixed the same day. The tracker this came out of is at aichangewatch.com https://aichangewatch.com/?src=devto — it records what AI vendors change in their own docs, which involves a lot of timestamps that have to be comparable across sources.