mixed text timestamp formats silently inflate report counts (ISO-T vs space form in text columns)

TL;DR

If one column holds timestamps as text in two formats, every datetime range filter and sort on that column is quietly wrong. Two writers, two formats: 2026-10-07T14:22:00Z (ISO with T separator) vs 2026-10-07 14:22:00 (legacy space form). Text comparison is bytewise, and the byte at position 11 decides: T (0x54) sorts above space (0x20). So a lower bound rendered in space form matches every ISO-format row from the same calendar day. Real case: a 6-hour window inflated from 321 to 616 rows (+92%), a 24-hour window from 1272 to 1752 (+38%). Fix: normalize BOTH sides of every comparison, converge to one writer format, and add a guard test.

The error

There is no error message. The query runs fine and returns the wrong number. The symptom is a report whose counts are too high for the requested window, or a date-ordered list that comes out ordered by date and then grouped by format instead of ordered by time.

In the real case, the event table had two writers inserting timestamps as text:

2026-10-07T14:22:10Z   -- writer A, ISO-UTC with T separator
2026-10-07 14:25:44    -- writer B, legacy space form

A filter written as WHERE ts >= '2026-10-07 08:00:00' (space form) matches every ISO row on 2026-10-07 after midnight, because at byte 11 ' ' (0x20) is below 'T' (0x54). The bound never excludes them.

Steps

1. Check whether the column holds two formats

SELECT DISTINCT substr(ts, 11, 1) AS sep, count(*) FROM events GROUP BY sep;

Expected output when broken: two rows, one with T and one with a space. If you only get one row, this skill does not apply; your inflation has a different cause.

2. Prove the inflation before fixing

SELECT count(*) FROM events WHERE ts >= '2026-10-07 08:00:00';
SELECT count(*) FROM events WHERE replace(ts, 'T', ' ') >= '2026-10-07 08:00:00';

Expected output: the second query returns fewer rows. The difference is the rows that were wrongly included by the unnormalized comparison.

3. Normalize BOTH sides of every comparison

Wrap the column in the filter, not just the bound:

SELECT * FROM events
WHERE replace(ts, 'T', ' ') >= '2026-10-07 08:00:00'
  AND replace(ts, 'T', ' ') < '2026-10-07 14:00:00';

Expected output: counts now match the true window. Normalizing only the bound does nothing; the column values are what differ.

4. Normalize ORDER BY the same way

SELECT * FROM events ORDER BY replace(ts, 'T', ' ') DESC LIMIT 50;

Expected output: a genuinely time-ordered list. The raw column orders all ISO rows before all space-form rows (or the reverse) for the same day, which looks sorted but is not.

5. Converge the writers to one format

Pick one canonical form going forward (ISO-UTC with T, e.g. strftime('%Y-%m-%dT%H:%M:%SZ','now') in SQLite) and change every writer to emit it. This is the actual fix; steps 3 and 4 are band-aids until the data converges.

Expected output: step 1's query eventually returns a single row.

6. Backfill the old rows

UPDATE events SET ts = replace(ts, 'T', ' ') WHERE ts LIKE '%T%';

Adjust to your canonical choice (here the space form). Expected output: step 1's query returns one row with zero mixed values. Run it in a transaction and spot-check a sample of rows first.

7. Add a guard test

Insert one row per legacy format and assert both land inside a 1-hour window query. Expected output: the test fails if a writer regresses to a second format, catching the bug before it reaches reports again.

When to use

• Report counts over a datetime window on a text timestamp column are too high, or grow when they should not.
• ORDER BY on a text timestamp column returns rows ordered by day, then grouped by format, instead of ordered by time.
• More than one writer, service, or migration path inserts into the same timestamp column.

When NOT to use

• The column is REAL or INTEGER (epoch storage). This is purely a text-comparison problem.
• The timestamps parse correctly but the timezone is wrong. That is a conversion bug, not a format-mixing bug.
• The wrongness comes from application-side date parsing (e.g. a date library misreading the format). Fix the parser, not the queries.

Tool and version compatibility

SQLite 3.x; the comparison logic is bytewise text collation, identical on every SQLite build. The same failure mode applies to any database that stores timestamps as text and compares them lexicographically (works the same in Postgres and MySQL text columns, though their native timestamp types avoid it).

Variant phrasings

• "sqlite datetime comparison wrong results"
• "timestamp text format comparison sqlite inflated counts"
• "report counts inflated datetime filter text column"
• "order by timestamp text column wrong order mixed formats"

Root cause

TEXT comparisons in SQLite are bytewise. For same-day timestamps, the separator byte at position 11 is the first byte that differs between the two formats, and T (0x54) compares greater than space (0x20). A space-form lower bound therefore matches every ISO-form row on the same day, and a sort groups by format after the date. The queries were always correct in intent; the column just did not hold one format.

Edge cases

• ISO forms with timezone suffixes (Z, +00:00) have more bytes after the time; normalizing only the T still leaves suffix mismatches. Canonicalize the full form, suffix included.
• Fractional seconds (14:22:10.500) compare fine within one format but add another mixing axis across formats.
• NULLs and empty strings sort outside both formats; they are not affected by this fix but can surprise counts the same way.
• If a writer emits ISO with a lowercase t, replace misses it. Use lower() first or fix the writer.