Store a Date-Only Value in a Database with Temporal
To store a date with no time or zone, use a DATE column and Temporal.PlainDate.toString() so the value can never shift across timezones. Part of Serializing and Storing Temporal Values.
Why this scenario is tricky
The clearest way to feel the bug is to trace one value. A user in Los Angeles enters their birthday, February 14th. Stored in a timestamptz column, it becomes 1990-02-14T00:00:00 at some assumed zone; if that assumption is UTC and the value is later read and formatted in Los Angeles, it displays as February 13th, because UTC midnight is the previous evening on the west coast. Nothing was corrupted — the instant is exactly what was stored — but the instant was never the right representation for a value that has no time.
This is the essential insight: a date-only value is not a moment, and forcing it into a moment-shaped column is what invites a zone to shift it. The DATE column exists precisely so that a calendar day can be stored as a calendar day.
A birthday, an invoice date, or a public holiday has no time and no zone — it is a pure calendar date, the same day for everyone who looks at it. The trouble begins when that value meets a column type designed for instants. Store 1990-02-14 in a timestamptz column and the database attaches a midnight and a zone; read it back in a different session zone and that midnight can slide to the previous or next day, so a birthday drifts and a report double-counts a boundary. The whole family of off-by-one date bugs comes from letting a zone touch a value that was never supposed to have one, and the fix is to keep such values in a column type that stores a date and nothing else.
A date-only value — a birthday, an invoice date, a holiday — has no time and no zone. Store it in a timestamptz column and the database attaches midnight in some zone; read it back in another zone and it can shift to the previous day. The whole class of off-by-one bugs comes from letting a zone touch a value that should never have had one.
Minimal working solution
The same reasoning extends to APIs: a date-only field in a JSON payload should travel as 'YYYY-MM-DD' and be parsed with Temporal.PlainDate.from, not as a full ISO timestamp that a consumer might interpret in its own zone. Civil in the database, civil on the wire, civil in memory — one representation end to end.
Temporal.PlainDate is the in-memory counterpart of a SQL DATE column: both represent a civil date with no time and no zone, so the mapping is lossless in both directions. Serialise with plainDate.toString(), which yields the ISO 'YYYY-MM-DD' a DATE column accepts directly, and read it back with Temporal.PlainDate.from(). Nothing in that round-trip introduces a time-of-day or an offset, so the value that goes in is exactly the value that comes out, for every reader in every zone. The discipline is simply to never let the value become an instant on either side of the boundary.
import { Temporal } from '@js-temporal/polyfill';
const dob = Temporal.PlainDate.from('1990-02-14');
const value = dob.toString(); // '1990-02-14' → store in a DATE column
const back = Temporal.PlainDate.from(value); // exact same civil dateFull production version
It helps to think about querying, not just storage, because a DATE column and a PlainDate also make range queries clean. "All invoices in March" is a half-open range [2024-03-01, 2024-04-01) compared against a DATE column, with no time-of-day or zone to muddy the boundaries — the same half-open discipline that governs civil date ranges in general. Store the value as a timestamp instead and those boundary comparisons acquire a hidden midnight and zone, so an invoice created late on the 31st in one zone can fall outside a March query run in another. Keeping the column civil keeps the queries civil, and the indexes on a DATE column stay small and exact.
In a real codebase the risk is not the happy path but the accidental coercion, so the boundary should refuse anything that is not a civil date. A thin mapping function that accepts a PlainDate or an ISO date string, validates it, and emits 'YYYY-MM-DD' gives you one place to guarantee no time or zone sneaks in. On the database side, choose DATE (Postgres, MySQL) rather than timestamp/timestamptz for these columns, and make sure the driver or ORM is configured to hand you back a plain string rather than eagerly constructing a Date — many drivers "helpfully" parse DATE columns into a Date at local midnight, which reintroduces exactly the drift you are trying to avoid. Where the ORM insists on a Date, intercept the column mapping and convert to PlainDate immediately.
Guard the boundary so a Date or ZonedDateTime can never sneak into the date-only field.
import { Temporal } from '@js-temporal/polyfill';
function toDateColumn(v: Temporal.PlainDate | string): string {
const d = typeof v === 'string' ? Temporal.PlainDate.from(v) : v;
return d.toString(); // always 'YYYY-MM-DD', never a time or zone
}Verification snippet
With these zone-invariance and range-query assertions in place, the DATE column and PlainDate mapping are protected against the accidental reintroduction of a zone by a future driver upgrade or ORM change.
A thorough test also covers the write path under different application zones, not just the read path, because some drivers apply the process zone when sending a value as well as when receiving it. Set the process TZ to something far from UTC, write a known date, and confirm the row holds that exact date rather than one shifted by the offset.
Pair that with a query test: insert dates on either side of a month boundary and confirm a half-open [first, next-first) range query returns exactly the expected rows regardless of the session zone, which proves the boundaries are civil and not secretly zoned.
The assertion that proves correctness is zone invariance: write a known date, then read it back under several session or process zones and confirm it never changes. Round-trip '1990-02-14' under UTC, a far-western zone like Pacific/Kiritimati, and a far-eastern one, and assert the value is byte-for-byte the same each time. Add a check that no time component appears in the stored or retrieved value, and that constructing the value from a string with a time attached is rejected at the boundary rather than silently truncated.
Common pitfalls
The whole topic reduces to a single rule worth repeating: a value that represents a calendar day must be a DATE column in the database and a Temporal.PlainDate in memory, and must never be stored as, coerced into, or compared against an instant at any layer. Hold that line through the schema, the driver, the ORM, the query, and the API, and the entire family of off-by-one date bugs disappears because no zone ever gets the chance to shift the value.
A final subtlety is ORMs and query builders that default date-typed columns to a Date object in the returned row. Even with a correct DATE column, the value can be re-zoned on the way out if the mapping layer constructs a local-midnight Date from it. Check how your specific ORM handles DATE, and where it insists on a Date, add a small transformer that converts to Temporal.PlainDate at the model boundary so nothing downstream ever sees a zoned instant standing in for a civil date.
The recurring rule across storage, querying, and mapping is one sentence: a value that means a calendar day should be a DATE column and a PlainDate in memory, and should never, at any layer, be represented as an instant.
One more trap deserves a mention: comparing a date-only value against a NOW() or a timestamp in SQL, which forces the database to coerce one side and can reintroduce a zone. When you need "is this civil date today," compute today's date in the relevant zone in application code and compare date to date, rather than letting the database compare a DATE against a zoned NOW(). The rule that keeps all of this straight is the same one that motivates the DATE column in the first place: a value that represents a calendar day should never be compared against, coerced into, or stored as an instant.
The headline pitfall is storing a date-only value in a timestamptz (or timestamp) column, which attaches a midnight and a zone and produces off-by-one reads. The second is new Date('1990-02-14'), which parses to UTC midnight — an instant, not a date — and drifts the moment it is formatted in another zone. The third is a driver or ORM that auto-parses DATE columns into a local-midnight Date; configure it to return a string, or convert to PlainDate at the seam. Use a DATE column, map it to PlainDate, and keep the value civil end to end.
Frequently Asked Questions
Why does a stored birthday show the wrong day for some users?
It was stored in a timestamp-with-zone column, which attaches midnight in one zone; read in another zone that midnight moves to the previous or next day. Store date-only values in a DATE column using Temporal.PlainDate so no zone is ever applied.
Should I use timestamptz for an invoice date?
No. An invoice date is a civil date with no time, so timestamptz only adds ambiguity. A DATE column holding PlainDate.toString() keeps it exact and identical for every reader regardless of their zone.
Why does a stored birthday show the wrong day for some users?
It was stored in a timestamp-with-zone column, which attaches midnight in one zone; read in another zone that midnight moves to the previous or next day. Store date-only values in a DATE column using Temporal.PlainDate.toString() so no zone is ever applied and the value is identical for every reader.
Should I use timestamptz for an invoice date?
No. An invoice date is a civil date with no time, so timestamptz only adds ambiguity and off-by-one risk. Use a DATE column holding PlainDate.toString(), which keeps the value exact and identical for every reader regardless of their session zone.
My database driver returns a Date for DATE columns and it is off by a day. How do I fix it?
Many drivers parse a DATE column into a JavaScript Date at local midnight, which drifts across zones. Configure the driver or ORM to return the raw string for DATE columns, or intercept the column mapping and convert straight to Temporal.PlainDate.from(), so the value never becomes a zoned instant.