driver-sql: every Field.date read from PostgreSQL is one day early when the process TZ is east of UTC — toDateOnly() reads UTC components off a local-midnight Date

[baozhoutao]

Summary

On PostgreSQL, every Field.date value read back through SqlDriver is shifted one calendar day earlier whenever the Node process runs in a timezone east of UTC (e.g. TZ=Asia/Shanghai). The value stored in the database is correct; the read path corrupts it.

We hit this in production (an MES app whose app container runs TZ=Asia/Shanghai, per the usual "container clock = business clock" deployment): users create a record today and every date field on it renders as yesterday, on both the Console and a custom H5 client. It is not a rendering bug — the REST payload already carries the wrong day.

Version: @objectstack/driver-sql@17.2.0 (current latest), pg@8.16.3, PostgreSQL 16.

Root cause

node-postgres materialises a date column (OID 1082) as a JS Date at local midnight. SqlDriver#toDateOnly() then formats that Date using UTC components:

toDateOnly(value) {
 if (value instanceof Date) {
 const y = value.getUTCFullYear();
 const m = String(value.getUTCMonth() + 1).padStart(2, "0");
 const d = String(value.getUTCDate()).padStart(2, "0");
 return `${y}-${m}-${d}`;
 }
 ...
}

Local midnight in UTC+8 is T-8h in UTC, i.e. the previous calendar day, so the UTC components name the wrong day. toDateOnly is the shared definition used by formatOutput (read), formatInput (write) and coerceFilterValue (filter), so the same skew reaches list payloads, single-record reads, and date filter comparands.

On SQLite the value round-trips as TEXT and never becomes a Date, which is why the bug is Postgres-only, and why it is invisible in dev.

Minimal reproduction

// TZ=Asia/Shanghai node repro.js
const { Client } = require('pg');
(async () => {
 const c = new Client({ connectionString: process.env.PG_URL });
 await c.connect();
 await c.query('drop table if exists t_date_repro');
 await c.query('create table t_date_repro (id int, d date)');
 await c.query("insert into t_date_repro values (1, '2026-08-24')");
 const v = (await c.query('select d from t_date_repro')).rows[0].d;
 // exactly what SqlDriver#toDateOnly() does for a Date instance:
 const out = `${v.getUTCFullYear()}-${String(v.getUTCMonth() + 1).padStart(2, '0')}-${String(v.getUTCDate()).padStart(2, '0')}`;
 console.log('pg returns :', v.toISOString());
 console.log('toDateOnly() => :', out);
 await c.end();
})();

--- TZ=Asia/Shanghai ---
pg returns : 2026-08-23T16:00:00.000Z
toDateOnly() => : 2026-08-23 <-- stored value was 2026-08-24

--- TZ=UTC ---
pg returns : 2026-08-24T00:00:00.000Z
toDateOnly() => : 2026-08-24 <-- correct

End-to-end on a real app, same rows, same browser, only the app process TZ changed:

	DB (psql)	REST GET /api/v1/data/:object/:id
TZ=Asia/Shanghai	apply_date = 2026-08-24	"apply_date": "2026-08-23"
TZ=UTC	apply_date = 2026-08-24	"apply_date": "2026-08-24"

Why this is worse than a display bug

The skewed read feeds writes. An afterUpdate hook that copies record.required_date into a child record persists the shifted value, so the wrong day is written back into the database and survives any later fix to the read path. We have production rows where the parent's required_date is 2026-08-26 and the child's copied plan_end_date is 2026-08-25.

Suggested fix

Read the calendar day from a Date the same way it was produced — with local components (getFullYear / getMonth / getDate) — or, more robustly, register a pg type parser for OID 1082 (and 1182, date[]) that returns the raw YYYY-MM-DD string so a date never becomes a Date in the first place. The latter also removes the DST/edge-case surface entirely and matches what the SQLite path already does.

Whichever direction is chosen, toDateOnly should be unambiguous about which clock a Date argument is expected to be on, since it is shared by the read, write and filter paths.

Workaround for app teams

Run the app process with TZ=UTC and keep the business timezone in OS_LOCALIZATION_TIMEZONE. Verified: the same rows then read back correctly, and autonumber date segments (which follow OS_LOCALIZATION_TIMEZONE, not the process clock) are unaffected. It does change any app code that formats timestamps from process-local Date components, so it is a mitigation rather than a fix.

[claude]

Triage (daily round, session session_01Kktexqp6uVuFMztvvTMf3V, 2026-08-24): lands in driver-sql toDateOnly() → domain:engine + pm:queue, Bug — P0-suspect: production-reported (TZ east of UTC ⇒ every Field.date read one day early; REST payload already wrong). Engine seat: this outranks queue-age peers via the Bug tier; treat as front-of-lane. Fix shape: read local components off the driver-materialized local-midnight Date (or pin the pg date parser); pin with a TZ-matrix test.

---

Generated by Claude Code

[claude]

Claim — domain:engine PM seat, session session_01RfyXxZ2WPjcjhuXpiQQc3y, round 2026-08-24 02:12Z.
Branch: claude/issue-11389-pg-date-tz-skew. Dispatched to one os-dev subagent.

Declared file surface (measured on origin/main @ d2b2381a36, sql-driver.ts = 13801 lines):

• packages/drivers/driver-sql/src/sql-driver.ts — toDateOnly() declared at 10676; its four consumers are coerceFilterValue (10728), presentReadValue (10980), formatInput (13030), formatOutput (13163). If the pg type-parser route is taken, the connection/dialect setup path is also in surface.
• test files under packages/drivers/driver-sql/src/.

Region carve-out declared so sibling cards can run concurrently on the same file: this claim does not touch SQL_AGGREGATE_FUNCTIONS / aggregate() / mapAggregateFunc (1216 / 7491 / 12400 — #11455), createColumn (12662 — #11431), or introspectForeignKeys (13401 — #11379).

Release criterion for the next claimant is "is any open PR still occupying this file", not "did this seat's PR merge".

---

Generated by Claude Code

[os-zhuang]

{
 "issue": 11389,
 "status": "done",
 "branch": "claude/issue-11389-pg-date-tz-skew",
 "pr": "https://github.com/objectstack-ai/objectstack/pull/11561",
 "premise_still_valid": true,
 "route_chosen": "b — register a connection-scoped pg type parser for OID 1082 (date) and 1182 (date[]) so a PostgreSQL date never becomes a JS Date",
 "route_rationale_from_measurement": "Route (a) (read local components in toDateOnly) was measured and is wrong. toDateOnly is shared by read/write/filter and the Dates they hand it are NOT on one clock. Measured on live PG16, only process TZ changed: a caller's new Date('2026-08-24') (write/filter) is UTC midnight, so its LOCAL components read 2026-08-23 under TZ=America/New_York; a caller's new Date(2026,7,24) is local midnight, so its UTC components read 2026-08-23 under TZ=Asia/Shanghai; and what pg handed the READ path was local midnight (2026-08-23T16:00Z under Asia/Shanghai, UTC comp 2026-08-23 = the bug). No single clock is correct for every Date that can arrive, so switching the shared helper wholesale would move the same one-day error onto the write and filter paths in the mirror direction. Route (b) removes the Date entirely from the read path, leaves exactly one clock in play, and mirrors an existing precedent in the same function chain (withUtcSession's mysql2 pin). Implemented via pool.afterCreate rather than pg.types.setTypeParser (which mutates the pg-types registry process-wide, reaching a host app's own pg clients — verified a plain pg.Client in the same process still receives a Date) and without importing pg (an optional peer dep; driver-sql builds through the shared root tsup.config.ts which does NOT set shims:true, so createRequire(import.meta.url) would emit import.meta verbatim into the CJS bundle and break require() at load). date[] reuses the connection's own text[] parser (OID 1009) — pg's array splitter with an identity element transform — so no array parsing is hand-rolled: NULL elements survive as null, {} yields [], nested arrays parse.",
 "files_touched": [
 "packages/drivers/driver-sql/src/sql-driver.ts",
 "packages/drivers/driver-sql/src/sql-driver-11389-date-tz-skew.test.ts (new)",
 "content/docs/protocol/objectql/types.mdx",
 ".changeset/pg-date-calendar-day-not-local-midnight.md (new)"
 ],
 "regions_touched": "Declared surface only: toDateOnly (docblock, behaviour unchanged) + the connection/dialect setup path (withConnectBound, new withPostgresCalendarDayAsText + POSTGRES_WIRE_CLIENTS / PG_OID_* statics beside withUtcSession) + tests. NOT touched: SQL_AGGREGATE_FUNCTIONS / aggregate() / mapAggregateFunc (#11455), createColumn (#11431), introspectForeignKeys (#11379). The four toDateOnly consumers (coerceFilterValue, presentReadValue, formatInput, formatOutput) were READ for the measurement and left unchanged — the fix is upstream of all four. One in-region refactor beyond the minimum: withConnectBound's early return on a client absent from DIALECT_CONNECT_TIMEOUT also skipped the session pins after it, so redshift (pg wire protocol, no timeout knob) silently opted out; the return is now a guarded block, sqlite's emitted config byte-identical either way.",
 "tests_added": "packages/drivers/driver-sql/src/sql-driver-11389-date-tz-skew.test.ts — 38 cases, three layers. (1) Mechanism, serverless: the real pool.afterCreate driven out of the real knex config with a recording connection — exactly OIDs 1082+1182 registered and nothing else, date returned byte-for-byte, date[] bound to the connection's own text[] parser BY IDENTITY, all five pg-wire client names covered, host afterCreate chained and its error surfaced, non-pg.Client degrades to a no-op, sqlite/mysql2 untouched. (2) Two-clock pin, serverless: 4 caller-Date shapes x 4 process zones on SQLite, through both write and filter paths — these PASS pre-fix by design, they guard the rejected route (a). (3) Live matrix on PG and MySQL: one connection, one row set, the process zone swept underneath it, asserted through find, distinct (the presentReadValue consumer) and a filter, plus a raw wire-form read for date[] and an untouched timestamptz. The zone list is UTC / Asia/Shanghai / America/New_York / Asia/Kolkata and the suite asserts it contains an east-of-UTC cell, a west-of-UTC cell and UTC before believing any of it.",
 "reverse_verification_output": "Pre-fix sql-driver.ts restored with `git restore --source=origin/main` (tree only, lone unstaged M, no MM). Mutation confirmed ON DISK before measuring: grep -c withPostgresCalendarDayAsText = 0 while grep -c getUTCFullYear = 1; git diff --stat 22 insertions / 180 deletions. No rebuild leg applies — driver-sql's vitest resolves the subject through a relative SOURCE import, not dist/. Result: 10 of 38 failed, predicted direction. Load-bearing failures: 'live postgres read the wrong calendar day under TZ=Asia/Shanghai: expected \\'2026-08-23\\' to be \\'2026-08-24\\'' · same under TZ=Asia/Kolkata · 'a pg `date` must not arrive as a JS Date: expected \\'object\\' to be \\'string\\''. The UTC and America/New_York cells PASSED on pre-fix source, and so did every MySQL cell — the PM's warning about a west-only matrix confirmed by measurement. Fix restored with `git checkout HEAD --`; git status --porcelain empty and git diff HEAD clean, i.e. byte-identical.",
 "live_db_state_found_and_configured": "Found DOWN, exactly as the dispatch warned. pg_lsclusters reported '16 main 5432 down' plus a stale pid file (pg_ctlcluster printed 'Removed stale pid file'); `service mysql status` reported 'MySQL is stopped'; no listener on 5432 or 3306. Started both and configured to CI parity: PG ALTER SYSTEM SET timezone='Asia/Shanghai' + pg_reload_conf, SHOW timezone read back 'Asia/Shanghai', postgres/postgres password set; MySQL SET GLOBAL time_zone='+08:00', @@global.time_zone read back '+08:00', database `conformance` created, root/root. TCP probes on 127.0.0.1:5432 and :3306 both OPEN. Left RUNNING deliberately — the sibling driver-sql lanes (#11431, #11455) are active in this container and tearing the services down mid-run would break them.",
 "mysql_reading_if_taken": "MySQL taken, and it corrects the card. The card says the bug is Postgres-only 'because on SQLite the value round-trips as TEXT' — true of SQLite, but it says nothing about MySQL, and mysql2 materialises a DATE at LOCAL midnight identically to pg (measured: iso 2026-08-23T16:00:00.000Z under TZ=Asia/Shanghai, UTC comp 2026-08-23). MySQL is nevertheless correct today for a reason the card never names: withUtcSession (#3942) already pins connection.timezone:'Z' on every mysql2 connection, and with that pin the same column arrives at UTC midnight. Measured side by side: opts={} -> UTCcomp 2026-08-23; opts={timezone:'Z'} -> UTCcomp 2026-08-24; opts={dateStrings:['DATE']} -> string '2026-08-24'. So the real root-cause shape is an asymmetry — MySQL had a connection-level pin, PostgreSQL had none — and this PR gives PostgreSQL its counterpart. No MySQL code change was needed; the MySQL cell is in the new matrix so that losing the mysql2 pin would reproduce this issue one dialect over.",
 "docs_corrected": "No hand-written doc stated the OLD (wrong) behaviour — content/docs/protocol/objectql/types.mdx and content/docs/data-modeling/queries.mdx both already say 'a Date collapses to its UTC calendar day', which stays true and is now the documented contract rather than an accident. Added one paragraph to types.mdx under the `date` type's Database mapping stating the user-visible guarantee this PR creates: the day you read back does not depend on the app process's timezone, PG parses date/date[] as text, MySQL connections are pinned to UTC, and running the process at TZ=UTC is no longer a prerequisite (the card's workaround). content/docs/releases/ untouched.",
 "changeset_bump": "minor — .changeset/pg-date-calendar-day-not-local-midnight.md. Sized as a behaviour change on a read path, not a bare patch: the corrected day changes what apps receive, and on PostgreSQL a RAW read (driver.execute / knex direct) now yields a string where it previously yielded a Date (values leaving find/findOne/aggregate/distinct were already YYYY-MM-DD strings and keep that type — only the day they name changes). Not declared BREAKING and no `major` (check-changeset-no-major is green): nothing an author writes is removed or renamed and there is no ADR-0087 migration to carry, so no adr-0087 marker is owed — check-adr-0087-registration green. The body carries the undo-your-workaround note and states plainly that rows already WRITTEN through the old skew are not repaired by this.",
 "summary": "PostgreSQL date reads are now process-timezone invariant. The driver registers connection-scoped pg type parsers (OID 1082 date -> raw YYYY-MM-DD text; OID 1182 date[] -> the connection's own text[] parser) via pool.afterCreate, so a `date` never becomes a JS Date and toDateOnly's UTC-component contract — which is correct for the write and filter paths — is left intact and now documented. Measured, not assumed: route (a) would have fixed the read and broken the write in the mirror direction. Pinned by a process-zone matrix that asserts it contains an east-of-UTC cell, which is precisely what the existing America/New_York-pinned CI job lacks.",
 "tests": "All on branch head 70c6c931ea, against live PG16 (timezone=Asia/Shanghai) + MySQL 8.0 (time_zone=+08:00), TZ=America/New_York, OS_EXPECT_LIVE_DIALECT_MATRIX=1 — CI parity; every heavy command through scripts/pm/os-verify-lock.sh. (1) `pnpm --filter @objectstack/driver-sql exec vitest run --maxWorkers=2` -> vitest's own verdict lines 'Test Files 126 passed (126)' / 'Tests 2549 passed (2549)'. (2) `pnpm --filter @objectstack/driver-sql typecheck` -> clean, no diagnostics. (3) eslint . --no-inline-config over the WHOLE repo, not a narrowing: exit 0 with eslint's own --format json accounting reporting 5031 files / 0 errors / 0 warnings, and both changed source files present in that population at e=0 w=0. (4) The gate set re-derived from the actual diff with `node scripts/pm/dispatch-gates.mjs` (no hand-fed paths): all 28 path-derived families green, plus the 5 convention-triggered families a new test file moves (query-options-erasure, engine-double-contract, where-matcher, type-check-coverage, nul-bytes). Two gates initially failed on an unbuilt-worktree artefact (@objectstack/lint's dist missing, then its dependency closure) and went green after building that closure — not defects in this change. Exit codes were captured by direct redirect before any pipe, never through a tail. Control-byte self-scan on all four changed files plus the PR body: no matches.",
 "open_questions": [],
 "out_of_scope_findings": [
 "filed as #11550: driver-sql's isSqlite/isPostgres/isMysql getters match fewer knex client spellings than knex accepts, so a valid `client: 'postgres'` (knex's own canonical name, of which pg/postgresql are the aliases) makes isPostgres false and silently loses every dialect-specific behaviour — including nowColumnDefault's UTC `date` expression default that #4022 added, which falls back to a bare CURRENT_TIMESTAMP resolving the calendar day in the SERVER's timezone. Same shape for `client: 'sqlite'` and for cockroachdb/redshift. The same file's DIALECT_CONNECT_TIMEOUT already covers the wider pg family, so two tables in one file answer 'is this Postgres?' differently. Filed unassigned, no label, left for triage; not fixed here because those getters are read from regions of sql-driver.ts held by the concurrent #11431 and #11455 claims."
 ],
 "remaining_out_of_scope": [
 "#11550 (above).",
 "pgnative (knex's pg-native client) is deliberately outside the covered client set and was NOT measured — nothing in this repo exercises it and it parses on the C side, where the JS parser registry may not apply. Stated in the PR body rather than claimed.",
 "Rows already WRITTEN through the old skew (the card's afterUpdate-hook example) still hold the wrong day. No driver can identify them — a date written correctly and one written through a skewed read are byte-identical on disk. Per-deployment data fix; named in both the changeset and the PR body."
 ],
 "blockers": []
}

---

Generated by Claude Code

---

Generated by Claude Code