I run AI Change Watch, 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');
Enter fullscreen mode Exit fullscreen mode
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.
The two halves of the comparison are different formats
crawl_runs.started_at is written by application code, as ISO 8601:
2026-08-24T17:40:41.965Z
Enter fullscreen mode Exit fullscreen mode
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
Enter fullscreen mode Exit fullscreen mode
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 storedT
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')
Enter fullscreen mode Exit fullscreen mode
Why it survived so long
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.
Where this bug actually lives
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:
const since = new Date(Date.now() - 86_400_000).toISOString();
// '2026-08-23T17:40:41.965Z' — same shape as the column
Enter fullscreen mode Exit fullscreen mode
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:
- no test covers it
- no reviewer reads it
- it exists for ninety seconds and produces a number you then repeat to someone
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.
If you can’t change the format on both sides
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'
Enter fullscreen mode Exit fullscreen mode
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)
Enter fullscreen mode Exit fullscreen mode
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.
How to find this in your own code
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
Enter fullscreen mode Exit fullscreen mode
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;
Enter fullscreen mode Exit fullscreen mode
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
comparison will not tell you when the assumption is wrong. It will answer the question you literally
asked, in a shape indistinguishable from the answer you wanted.
Found 2026-08-24, fixed the same day. The tracker this came out of is at
aichangewatch.com — it records what AI vendors change in their
own docs, which involves a lot of timestamps that have to be comparable across sources.