MySQL: Field.datetime maps to TIMESTAMP (range ends 2038-01-19) and binds a Date in the process-local timezone

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.datetime` cannot represent an instant past 2038-01-19

createColumn maps datetime to table.timestamp(name) (packages/plugins/driver-sql/src/sql-driver.ts). knex's MySQL column compiler emits a literal timestamp:

// knex/lib/dialects/mysql/schema/mysql-columncompiler.js
timestamp(precision) {
  return typeof precision === 'number' ? `timestamp(${precision})` : 'timestamp';
}

MySQL TIMESTAMP is a 32-bit epoch: 1970-01-01 00:00:01 UTC through 2038-01-19 03:14:07 UTC. Any Field.datetime outside that window — a contract end date, a subscription expiry, a retention horizon, a pre-1970 birth-adjacent timestamp — is rejected or truncated depending on sql_mode. On SQLite and Postgres the same field accepts it (the driver's own bucket tests carry a 1969-12-31T23:59:59.999Z fixture that MySQL cannot store at all).

There is also no precision, so sub-second detail is discarded: TIMESTAMP defaults to 0 fractional digits, and the platform's canonical form carries milliseconds.

Likely fix: datetime(3) — MySQL's DATETIME spans 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 `Date` is serialised in the process-local zone

The knex connection config sets no timezone, so mysql2 uses its default 'local' and renders a bound JS Date as a wall-clock string in the Node process's zone. MySQL then interprets that against the session time_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 on Asia/Shanghai). formatInput now sends a canonical zone-explicit …Z string 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 whether timezone: '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 on OS_TEST_POSTGRES_URL and skipped when unset. A MySQL twin (OS_TEST_MYSQL_URL) should assert:

a Field.datetime past 2038 and before 1970 round-trips to the same instant;
millisecond precision survives a write/read cycle;
a Date, an ISO-Z string and a zone-naive string all record the same instant, with the process timezone and the session time_zone both set to something other than UTC;
a bare YYYY-MM-DD filter 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.

fix(driver-sql): store Field.datetime as DATETIME(3) UTC on MySQL (#3942

Fixed on claude/sqlite-datetime-filter-empty-hradlv (0b92b58), and verified against a real server — MariaDB 10.11 with default_time_zone = '+08:00', Node process on America/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 T separator nor the Z suffix 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 fails
JS `Date`	⚠️ stored, but truncated to whole seconds
zone-naive string	⚠️ stored 12 hours off (host zone −4 and server zone +8 compounding)
2040 / 1960	❌ rejected — `TIMESTAMP` is a 32-bit epoch

So Field.datetime writes over REST were already broken on MySQL, independent of #3912. And #3912's canonicalisation extended that failure to the Date path 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 of TIMESTAMP — 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 keeps timestamptz: asking for precision 3 there would reduce it from microseconds. created_at/updated_at take the same type, since the registry declares them Field.datetime and 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' via pool.afterCreate for the server. This is what makes the wall clock be the instant, and it keeps a not-yet-migrated TIMESTAMP column correct too. An explicit host choice is left alone; an existing afterCreate is chained rather than replaced.
storageDatetimeValue respells 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.

migrateMysqlDatetimeColumns widens legacy TIMESTAMP columns at schema sync, restating the audit DEFAULT because MySQL drops it on MODIFY. Failure policy matches the SQLite backfill: logged and swallowed, because a TIMESTAMP column keeps working (same literal, UTC session) and merely keeps its range and precision limits.

Verified

Migration: the ALTER moves 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 via OS_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 test

against 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

fix(cli,driver-sql): os migrate plan lists the datetime storage conve…

feat(spec,ci): temporal hooks onto the IDataDriver contract; conforma…

fix(driver-sql): Field.time canonical storage — HH:MM:SS[.fff] wall-c…

docs: correct temporal-field storage and filter semantics in the Obje…

fix(driver-sql): give Field.datetime one UTC storage form per dialect (…

fix(driver-sql): stamp updated_at at the audit column's own precision…

test(driver-sql): budget the 8th live-cell hook, reached through `raw…

Navigation Menu

Repository navigation

MySQL: Field.datetime maps to TIMESTAMP (range ends 2038-01-19) and binds a Date in the process-local timezone #3942

1. `Field.datetime` cannot represent an instant past 2038-01-19

2. No connection timezone, so a `Date` is serialised in the process-local zone

Suggested verification

The measured baseline was worse than "2038 + timezone ambiguity"

What changed

Verified

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

MySQL: Field.datetime maps to TIMESTAMP (range ends 2038-01-19) and binds a Date in the process-local timezone #3942

Description

1. Field.datetime cannot represent an instant past 2038-01-19

2. No connection timezone, so a Date is serialised in the process-local zone

Suggested verification

Activity

os-zhuang commented on Jul 29, 2026

The measured baseline was worse than "2038 + timezone ambiguity"

What changed

Verified

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions

1. `Field.datetime` cannot represent an instant past 2038-01-19

2. No connection timezone, so a `Date` is serialised in the process-local zone