Dates and time zones: store UTC, keep the user's zone, convert at display

Time bugs are the quietest bugs. The system works every day until an overseas customer gets a reminder an hour late, or the daily report puts 1 a.m. orders on the wrong day. Almost every time, the cause is a time stored without a zone, or converted in the wrong place. This article gives three rules that work in any system, with Postgres, Python and JavaScript code whose output was run for real.

Tested on Windows 11, Python 3.12 + tzdata 2026.5, Node.js 20 + temporal-polyfill 1.0.6, PostgreSQL 18.6 (run through PGlite 0.5.11).

An instant is not a calendar date

Before choosing a column type, ask what the value means.

Kind	Meaning	Examples	Store as
Instant	the same moment everywhere	order placed, login, message sent	timestamptz (UTC)
Calendar date	a day, no time, no zone	birthday, public holiday, contract due date	date
Future local time	wall-clock time at a place	every Monday 15:00 Bangkok time	local time + zone name

Birthdays are the classic trap. Store 28 February as 1990-02-28 00:00+07 and what is actually stored is 1990-02-27 17:00 UTC; a server running in UTC shows the birthday as 27 February. A birthday has no time of day, so it belongs in a date.

Business days work the same way. “Ships within 3 business days” is counted on the Thai calendar: convert the order instant to a date in Asia/Bangkok first, then count days against a holiday table stored as date.

Three rules

1. Store instants in UTC. In Postgres that means timestamptz. In APIs, always send ISO 8601 with Z or an offset, e.g. 2026-10-22T03:30:00Z
2. Store the user’s IANA zone name, such as Asia/Bangkok or Europe/London, on their profile, not +07:00. An offset is one day’s value; the zone name carries the year’s rules, DST included
3. Convert to local time only at display, at the edge of the system (web page, email, PDF). Everything inside works in UTC

It is the same idea as storing money in the smallest unit and formatting it at display: keep an unambiguous canonical value, and let the presentation layer make it human.

Store UTC, remember the user’s zone, convert at the edge. Those three rules prevent nearly every time bug.

PostgreSQL: timestamptz does not store a zone

The name timestamp with time zone misleads. The Postgres docs say the value is converted to UTC on input and the original zone is not retained; on output it is converted to the session’s TimeZone setting. Plain timestamp silently ignores any zone you send, so do not use it for instants.

SET TIME ZONE 'UTC';
CREATE TABLE bookings (
  id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  starts_at timestamptz NOT NULL,
  user_tz   text NOT NULL DEFAULT 'Asia/Bangkok'
);
INSERT INTO bookings (starts_at) VALUES ('2026-10-22 10:30+07');

SELECT starts_at, starts_at AT TIME ZONE user_tz AS local_time FROM bookings;
-- starts_at              | local_time
-- 2026-10-22 03:30:00+00 | 2026-10-22 10:30:00

SET TIME ZONE 'Asia/Bangkok';
SELECT starts_at FROM bookings;
-- 2026-10-22 10:30:00+07   (same value, shown per session)

timestamptz AT TIME ZONE returns a zone-less local time. Use it for display, or to ask “which day is this in Thailand”, e.g. (starts_at AT TIME ZONE 'Asia/Bangkok')::date for daily reports. Skip that and every order between 00:00 and 06:59 Bangkok time lands on the previous day, because it is still the previous day in UTC. Time-series data follows the same rule; see the TimescaleDB article.

Adding 24 hours is not “tomorrow”

Thailand has no DST (daylight saving time, moving clocks by season), but customers and teams in the US and Europe do. In 2026 New York springs forward on 8 March, so that day has only 23 hours.

Step by step: a daily reminder at 09:00 New York time, starting 7 March 2026.

1. 7 March 09:00 in New York, offset -05:00 = 14:00 UTC
2. Add 24 hours = 8 March 14:00 UTC
3. On 8 March New York is at -04:00, so 14:00 UTC is 10:00 local. The user’s reminder is an hour late
4. Add one calendar day = 8 March 09:00 New York = 13:00 UTC. Correct, and only 23 real hours after the previous one

Both are right for some job. A token that expires in 24 hours wants step 2. An appointment people remember by the clock wants step 4. Postgres does step 4 with interval '1 day' in the session’s zone, or with an explicit zone via date_add() (PostgreSQL 16 and later):

SELECT date_add('2026-03-07 09:00-05'::timestamptz, interval '1 day', 'America/New_York');
-- = 2026-03-08 13:00 UTC = 09:00 in New York

Closer to home: a weekly 15:00 Bangkok meeting with a London team. London leaves DST on 25 October 2026.

Date	Bangkok	London	Gap
24 Oct 2026	15:00	09:00 (BST, +01:00)	6 hours
26 Oct 2026	15:00	08:00 (GMT, +00:00)	7 hours

So a recurring meeting is stored as “15:00 in Asia/Bangkok, every Monday”, and each occurrence’s instant is computed when it comes up. Precompute a year of instants and store them, and the day some country changes its clock rules (it happens regularly, and reaches you through tzdata updates) the whole stored series is wrong.

Python: zoneinfo, and the Windows trap

zoneinfo has been in the standard library since Python 3.9.

from datetime import datetime, timedelta, timezone
from zoneinfo import ZoneInfo

ny = ZoneInfo("America/New_York")
d = datetime(2026, 3, 7, 9, 0, tzinfo=ny)

print(d + timedelta(hours=24))   # 2026-03-08 09:00:00-04:00  (wall-clock arithmetic)
print((d.astimezone(timezone.utc) + timedelta(hours=24)).astimezone(ny))
                                 # 2026-03-08 10:00:00-04:00  (24 real hours)

bkk = ZoneInfo("Asia/Bangkok")
t = datetime(2026, 10, 22, 3, 30, tzinfo=timezone.utc).astimezone(bkk)
print(t)                         # 2026-10-22 10:30:00+07:00

Note that timedelta(hours=24) on a ZoneInfo-aware datetime adds wall-clock time, not elapsed time. For 24 real hours, convert to UTC first.

The trap I hit while writing this: on Windows the code above fails with ZoneInfoNotFoundError: 'No time zone found with key America/New_York', because Windows has no IANA database. The Python docs recommend declaring tzdata as a dependency for cross-platform projects:

pip install tzdata

JavaScript: Date today, Temporal next

Date holds an instant as milliseconds since the epoch, which is fine, but parsing has a trap. MDN documents that a date-only string is parsed as UTC while a date-time string without an offset is parsed as local time:

// on a machine set to Asia/Bangkok
new Date('2026-10-22').toISOString();        // '2026-10-22T00:00:00.000Z'
new Date('2026-10-22T00:00').toISOString();  // '2026-10-21T17:00:00.000Z'

Two near-identical lines, seven hours apart, and the second one changes with the machine it runs on.

The long-term fix is Temporal, a new API with one type per meaning: Temporal.Instant (an instant), Temporal.PlainDate (a calendar date), Temporal.ZonedDateTime (local time plus a zone name).

const start = Temporal.ZonedDateTime.from('2026-03-07T09:00[America/New_York]');
start.add({ days: 1 }).toString();   // '2026-03-08T09:00:00-04:00[America/New_York]'
start.add({ hours: 24 }).toString(); // '2026-03-08T10:00:00-04:00[America/New_York]'

Status as of October 2026: the proposal is Stage 4, it ships in Chrome/Edge 144 and Firefox 139, and it is on by default in Node.js 26, but stable Safari does not have it yet (Technology Preview only), so MDN does not list it as Baseline. For web code today use a polyfill such as temporal-polyfill or @js-temporal/polyfill, loaded only when typeof Temporal === 'undefined'.

The Thai Buddhist year is a display concern

The Buddhist Era year is CE + 543 (2026 + 543 = 2569). Always store CE, convert at display, and do not do the arithmetic yourself: Intl does it, and the th-TH locale uses the Buddhist calendar by default.

const d = new Date('2026-10-22T03:30:00Z');
const opt = { dateStyle: 'medium', timeStyle: 'short', timeZone: 'Asia/Bangkok' };
new Intl.DateTimeFormat('th-TH', opt).format(d);              // '22 ต.ค. 2569 10:30'
new Intl.DateTimeFormat('th-TH-u-ca-gregory', opt).format(d); // '22 ต.ค. 2026 10:30'

If users type a Buddhist year into a form, convert it to CE at the input layer and range-check it (a birth year of 2569 vs 2026 is a common 543-year bug). One note for historical data: before 1941 the Thai year started on 1 April, so dates from 1 January to 31 March in that period differ from CE by 542, not 543. Ordinary apps never see this; archive projects do.

Common mistakes

• timestamp instead of timestamptz, then two servers in different zones write mixed data nobody can untangle
• Birthdays stored as timestamps, shifting a day when shown in another zone
• Storing +07:00 instead of a zone name: fine for Thailand without DST, broken the moment you have users abroad
• Daily reports cut at UTC midnight, pushing early-morning Thai orders to the previous day. Cut with AT TIME ZONE in the business’s zone
• Adding 86,400 seconds instead of one day for anything people remember by the clock
• Stale or missing tzdata: Windows needs the tzdata package, so do slim Docker images, and update it when countries change rules
• Storing the Buddhist year, then sorting and age calculations are off by 543 years

Instants in UTC, birthdays as dates, future appointments as local time plus a zone name. Ask what the value means before you pick its type.

Sources

• Date/Time Types — PostgreSQL 18 documentation
• PostgreSQL 16 release notes (date_add, date_subtract) — postgresql.org
• zoneinfo — IANA time zone support, Python documentation
• Temporal — MDN Web Docs and Temporal proposal — TC39 GitHub
• Temporal browser support — caniuse.com
• Node.js 26.0.0 release notes — nodejs.org
• Date.parse() — MDN Web Docs
• Thai solar calendar — Wikipedia