Comparing an ISO timestamp against datetime('now') widened a one-hour window to nearly 18 hours

Character 11 is where the comparison decides. For any row recorded on the cutoff day, the first ten bytes of the date part match, and then the column's `T` meets the space in SQLite's own output ^[3][4]. `T` is 0x54, the space is 0x20, so the row is greater whatever the clock said ^[6]. A run from 00:03 that morning counted as inside the last hour ^[7].

So the query ran over a different window than the one stated. The cutoff string was `2026-08-24 16:54:52`, so the query executed at 17:54:52, and every row back to midnight passed the test: about 17 hours 55 minutes of data, not one hour ^[4][1]. Of the 1,252 rows returned, 1,184 fell outside the hour, which is 94.6 per cent of the result ^[2].

Which way it fails depends on the query. On a freshness check the count comes out too big, so a stalled crawler still looks busy, and the author of AI Change Watch calls this the dangerous direction: the check whose whole job is to report a stop is biased toward saying everything is fine ^[9]. On a windowed audit the same defect pulls in the entire boundary day, so a `-1 day` or `-7 day` review is wider than you stated and never narrower ^[10]. "Nobody notices being handed extra," the author wrote ^[11].

Nothing in the output flags it. There is no type error and no coercion warning, and the result set does not look suspiciously empty: the rows come back real and formatted exactly like the ones you wanted ^[12].

The 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, and `new Date(Date.now() - 86_400_000).toISOString()` emits the same `T`/`Z` shape as the column ^[13][14]. The defect lived entirely in hand-typed operational queries. No test covers those and no reviewer reads them; they exist for about ninety seconds before producing a number you repeat to someone ^[15]. Several "runs in the last hour" figures the author had quoted earlier were inflated by it ^[16]. "The application was healthy the whole time; the instrument was wrong," the author wrote ^[17].

Repairing this cheaply means making the cutoff the same shape as the column: `strftime('%Y-%m-%dT%H:%M:%SZ', 'now', '-1 hour')` returns 68 ^[8]. For a script already carrying twenty scattered `datetime()` calls, `replace(datetime('now', '-1 hour'), ' ', 'T') || 'Z'` is the mechanical edit, and the author prefers `strftime` because it states the format it produces instead of repairing one ^[18]. Changing stored data would be a migration; changing a cutoff is a one-line edit ^[24].

Structurally, stop storing dates as text. SQLite's documentation offers three conventions, ISO-8601 TEXT, Julian day as REAL and Unix epoch as INTEGER, and this class of bug only exists in the first ^[20]. Against an integer column, `WHERE started_at_ms > (unixepoch('now', '-1 hour') * 1000)` compares numbers, so there is no format left to disagree about ^[19]. The cost is legibility: `1787143241965` in a console tells you nothing, and every hand-written query needs a conversion to read ^[21]. For a table he mostly reads by eye, the author kept the text column and fixed the cutoffs; for one he only ever compares, he says he would not ^[22].