Answer

Diagnosed and verified. Written to ~/sqlite-text-timestamp-vs-go-local-time-window.md.

SQLite UTC TEXT timestamps vs. Go local time.Time window

Class: sqlite-text-timestamp-vs-go-local-time-window · Ref: consensus rate_limiter, QA-CONSENSUS-23

Root cause

• CURRENT_TIMESTAMP / datetime('now') write UTC text ('2026-10-01 22:27:08') into a TEXT column.
• SQLite compares that column lexicographically. A Go time.Time bound by the driver serializes to its String() form in the process-local zone, e.g. '2026-10-01 18:27:08 -0400 EDT'.
• Only the leading 19 bytes matter to the string compare, so the effective cutoff is the local wall clock read as UTC — the window shifts by the host UTC offset. Positive offsets drop in-window rows; negative offsets admit out-of-window rows. Invisible on a UTC CI host.

Exact fix

Normalize every Go time.Time at the SQLite boundary:

// BEFORE
tx.ExecContext(ctx, `DELETE FROM rate_limit_events WHERE created_at < ?`, cutoff)

// AFTER
tx.ExecContext(ctx, `DELETE FROM rate_limit_events WHERE created_at < ?`, cutoff.UTC())

Belt-and-suspenders — compare numerically, no text/timezone involved:

tx.ExecContext(ctx,
    `DELETE FROM rate_limit_events WHERE unixepoch(created_at) < ?`, cutoff.Unix())

Guardrails: never write datetime('now','localtime'); require .UTC()/.Unix() for any time.Time bound against a TEXT timestamp; run DB tests under a positive and negative offset, not just TZ=UTC.

Verification (reproduced)

Two UTC rows (now, now+2h) with window [now-5m, now]; correct answer is 1.

TZ=UTC    (UTC+00.0)  local=1  utc=1  unixepoch=1  want=1   # bug hidden on UTC
TZ=JST    (UTC+09.0)  local=0  utc=1  unixepoch=1  want=1   # in-window row dropped
TZ=EDT    (UTC-04.0)  local=0  utc=1  unixepoch=1  want=1
TZ=+1030  (UTC+10.5)  local=0  utc=1  unixepoch=1  want=1
TZ=PDT    (UTC-07.0)  local=0  utc=1  unixepoch=1  want=1

local is wrong in every non-UTC zone; from.UTC()/to.UTC() and unixepoch() are correct in all zones. Reproducer and runnable verify.go are in the document, validated with Go 1.26 + modernc.org/sqlite + SQLite 3.53.