Repository navigation
MySQL: Field.datetime maps to TIMESTAMP (range ends 2038-01-19) and binds a Date in the process-local timezone #3942
Description
Activity
- added a commit that references this issue
on Jul 29, 2026 Fixed on
claude/sqlite-datetime-filter-empty-hradlv(0b92b58), and verified against a real server — MariaDB 10.11 withdefault_time_zone = '+08:00', Node process onAmerica/New_York. So the source-analysis caveat in the issue body no longer applies, and one finding turned out to be materially worse than described.The measured baseline was worse than "2038 + timezone ambiguity"
MySQL accepts neither the
Tseparator nor theZsuffix in a datetime literal. Before this branch:write shape result ISO string (what every REST/JSON write carries — JSON has no Date)❌ Incorrect datetime value, statement failsJS Date⚠️ stored, but truncated to whole secondszone-naive string ⚠️ stored 12 hours off (host zone −4 and server zone +8 compounding)2040 / 1960 ❌ rejected — TIMESTAMPis a 32-bit epochSo
Field.datetimewrites over REST were already broken on MySQL, independent of #3912. And #3912's canonicalisation extended that failure to theDatepath too — a regression that branch had to fix before it could merge, which is why this landed there rather than separately.What changed
Logical canon unchanged; only the physical spelling differs per dialect.
DATETIME(3)instead ofTIMESTAMP— range 1000..9999, milliseconds kept, and no timezone conversion of its own, so the column holds the UTC wall clock the driver writes (the ServiceNow model). Postgres deliberately keepstimestamptz: asking for precision 3 there would reduce it from microseconds.created_at/updated_attake the same type, since the registry declares themField.datetimeand they are what most list views sort by.- Connection pinned to UTC on both layers —
connection.timezone = 'Z'for mysql2,SET time_zone = '+00:00'viapool.afterCreatefor the server. This is what makes the wall clock be the instant, and it keeps a not-yet-migratedTIMESTAMPcolumn correct too. An explicit host choice is left alone; an existingafterCreateis chained rather than replaced. storageDatetimeValuerespells the canonical instant as a MySQL literal for the bind, on the write path and the filter path alike so the two cannot disagree. Deliberately strict — only an exactly-canonical string is rewritten, so an unparseable value or a year outside 1000..9999 reaches MySQL untouched and fails loudly instead of being silently reinterpreted.
migrateMysqlDatetimeColumnswidens legacyTIMESTAMPcolumns at schema sync, restating the auditDEFAULTbecause MySQL drops it onMODIFY. Failure policy matches the SQLite backfill: logged and swallowed, because aTIMESTAMPcolumn keeps working (same literal, UTC session) and merely keeps its range and precision limits.Verified
Migration: the
ALTERmoves no stored instant, correctly-stored legacy rows round-trip exactly, post-2038 and pre-1970 instants then store, and re-running is a no-op.Worth stating plainly, same as the Postgres caveat in #3912: the migration cannot repair instants the old timezone-ambiguous write path recorded wrongly — that information is gone. It preserves what is on disk.
Regression cover is
sql-driver-datetime-mysql-storage.test.ts, opt-in viaOS_TEST_MYSQL_URL(CI provisions no server) and asserting it is pointed at a non-UTC server so it cannot pass vacuously. 10 of its 13 cases fail without the change. Reproduce with:OS_TEST_MYSQL_URL=mysql://root@127.0.0.1:3306/test \ TZ=America/New_York pnpm --filter @objectstack/driver-sql testagainst a server started with
default_time_zone='+08:00'.Rationale is recorded as ADR-0053 addendum D-B4. Leaving this open until the branch merges.
Generated by Claude Code
- added 4 commits that reference this issue
on Jul 30, 2026 - added a commit that references this issue
on Jul 30, 2026 - added a commit that references this issue
on Aug 23, 2026 - added a commit that references this issue
on Aug 23, 2026 - added a commit that references this issue
on Sep 9, 2026
Found while evaluating whether #3912 (SQLite datetime storage form) applied to the other dialects. SQLite and Postgres are addressed on
claude/sqlite-datetime-filter-empty-hradlv; MySQL is not, and has two defects of its own.This is source analysis, not measurement. No MySQL was installable in the environment where the evaluation ran (Docker unavailable, no server binaries), so both findings below need confirming against a real server before a fix lands.
1.
Field.datetimecannot represent an instant past 2038-01-19createColumnmapsdatetimetotable.timestamp(name)(packages/plugins/driver-sql/src/sql-driver.ts). knex's MySQL column compiler emits a literaltimestamp:MySQL
TIMESTAMPis a 32-bit epoch: 1970-01-01 00:00:01 UTC through 2038-01-19 03:14:07 UTC. AnyField.datetimeoutside that window — a contract end date, a subscription expiry, a retention horizon, a pre-1970 birth-adjacent timestamp — is rejected or truncated depending onsql_mode. On SQLite and Postgres the same field accepts it (the driver's own bucket tests carry a1969-12-31T23:59:59.999Zfixture that MySQL cannot store at all).There is also no
precision, so sub-second detail is discarded:TIMESTAMPdefaults to 0 fractional digits, and the platform's canonical form carries milliseconds.Likely fix:
datetime(3)— MySQL'sDATETIMEspans 1000-01-01 to 9999-12-31, and(3)keeps milliseconds. That is a column-type migration on existing tables, which is why it wants its own change rather than riding along with #3912.2. No connection timezone, so a
Dateis serialised in the process-local zoneThe knex connection config sets no
timezone, so mysql2 uses its default'local'and renders a bound JSDateas a wall-clock string in the Node process's zone. MySQL then interprets that against the sessiontime_zone. Two app servers in different zones writing the same instant record different values, and the value depends on deployment topology rather than the data.This is the same family as the Postgres finding fixed in #3912 (there, a zone-naive string was resolved against the server's
TimeZone, measured 8 hours off onAsia/Shanghai).formatInputnow sends a canonical zone-explicit…Zstring on every dialect, which should remove the ambiguity for string binds — but MySQL only accepts an offset in a datetime literal from 8.0.19 onward, so the behaviour on older servers needs checking, as does whethertimezone: 'Z'should be pinned on the connection regardless.Suggested verification
The opt-in harness added for Postgres in #3912 is the template —
packages/plugins/driver-sql/src/sql-driver-datetime-postgres-timezone.test.ts, gated onOS_TEST_POSTGRES_URLand skipped when unset. A MySQL twin (OS_TEST_MYSQL_URL) should assert:Field.datetimepast 2038 and before 1970 round-trips to the same instant;Date, an ISO-Zstring and a zone-naive string all record the same instant, with the process timezone and the sessiontime_zoneboth set to something other than UTC;YYYY-MM-DDfilter comparand means UTC midnight, matching SQLite and Postgres — the cross-dialect divergence [17.0.0-rc.0] SQLite datetime window filters return empty: filter comparands coerced to epoch-ms while writes store ISO TEXT #3912 measured.