Skip to main content

Datetimes and time zones — the same instant under different names

Daily sales aggregation picked up orders 9 hours earlier than reality. A reservation time saved shifted by 1 hour after DST switch. Computing "1 month later" gave March 3.

None error out. Silently the numbers differ — that kind of failure. The cause is usually treating datetime as one kind of value. It's actually at least 3 kinds, and mixing breaks things.

What you'll learn in this chapter​

  • Datetimes split into instant, local time, duration
  • Why offsets and time zones are different
  • How past records and future events differ in storage
  • Day boundaries move with time zones
  • Same string interpreted differently by form
  • Calendar arithmetic isn't straightforward addition
Prerequisites

No specific prerequisite. The daily aggregation discussion touches "patterns that stop indexes from being used" in Indexes; reading first ties the range-spec discussion together.

What this chapter does not cover​

TopicSee
How to return datetimes in API responsesLaravel API Development Guide — API resources
Which time zone to judge scheduled runs inLaravel API Development Guide — Task scheduler
The design choice of column type as TIMESTAMP WITH TIME ZONEDatabase Design Guide — The Importance of Database Design
How indexes work on date-range searchesIndexes

Distinguish three separate things​

What's lumped as "datetime" splits into three by nature.

KindRefers toExample
InstantOne point from anywhere in the worldOrder confirmation time, log time
Local time"Hour on that region's clock". Not an instant until place is setReservation 14:00, opening 9:00
DurationA length. No starting point30 min, 3 days, 1 month

Mixing breaks things here. "2026-08-28 14:00" is local time, not an instant. Only after specifying where does it fix to one world point.

Offset isn't time zone​

Notations like +09:00 are sometimes called "time zone", but they're different.

  • Offset is "how many hours off from UTC" — a result
  • Time zone is "when to use which offset" — a rule

Rules change. Regions with DST change twice a year; law changes can shift offsets themselves.

const offsetOf = (tz: string, iso: string) =>
  new Intl.DateTimeFormat('en-US', {timeZone: tz, timeZoneName: 'longOffset'})
    .formatToParts(new Date(iso))
    .find((p) => p.type === 'timeZoneName')!.value;

for (const tz of ['America/New_York', 'Asia/Tokyo']) {
  console.log(tz.padEnd(17), 'Jan', offsetOf(tz, '2026-01-15T12:00:00Z'), ' Aug', offsetOf(tz, '2026-08-15T12:00:00Z'));
}
America/New_York  Jan GMT-05:00  Aug GMT-04:00
Asia/Tokyo        Jan GMT+09:00  Aug GMT+09:00

The same America/New_York has different offsets in January and August. Saving -05:00 can't recover "New York".

RFC 9557 was created to fill this gap, allowing bracketed time zone names in RFC 3339 syntax:

This is a pressing issue for applications that handle each such instant in time with an associated time zone name in order to take into account events such as daylight saving time transitions.

— RFC 9557 — Date and Time on the Internet: Timestamps with Additional Information (April 2024)

Format: 1996-12-19T16:39:57-08:00[America/Los_Angeles]. Carries both offset and name.

What to store depends on past vs. future​

Storage rules aren't one. Split by whether it's a fixed past fact or a future scheduled event.

TargetStoreReason
Occurred (order, login, log)Instant. In UTCWon't move. What clock to view is display-side
Future events tied to human clocks (reservation, meeting, local deadline)Local time + time zone nameIf rules change, the corresponding instant should change too
Values whose meaning is complete with date alone (birthday, anniversary)Date as-is. No timeAdding time creates "0 o'clock of where" problem

Row 2 is counterintuitive. "Future events can also be stored in UTC" seems fine, but time zone rules changing breaks it. If DST is abolished, "next year's 10:00 meeting" stored in UTC becomes 9:00 or 11:00. What people expect is "10:00 on the local clock".

Row 3 too. Storing a birthday as 2000-05-03T00:00:00Z becomes May 3 at 9:00 in Japan; across time zones, the date itself shifts. Values holding only date should have no time, no time zone.

Whether it's row 2 is decided by "is it a promise tied to a human clock". "Meet at local 10:00" is human-clock, so row 2. Meanwhile "expires 1 hour after issuance" isn't tied to any human clock, so even future values are held as instants. Token expiries fall here (Authentication and authorization).

Strings are interpreted by form​

Strings that look the same are interpreted differently by form. ECMAScript defines:

When the UTC offset representation is absent, date-only forms are interpreted as a UTC time and date-time forms are interpreted as a local time.

— ECMAScript — 21.4.3.2 Date.parse

Date-only is UTC; date-time without offset is local time — an asymmetric rule.

console.log('Runtime time zone', Intl.DateTimeFormat().resolvedOptions().timeZone);

const dateOnly = new Date('2026-08-28');          // Date-only → read as UTC
const dateTime = new Date('2026-08-28T00:00:00'); // Date + time → read as local
const withOffset = new Date('2026-08-28T00:00:00+09:00');

console.log('Date only  ', dateOnly.toISOString());
console.log('With time  ', dateTime.toISOString());
console.log('With offset', withOffset.toISOString());
console.log('Diff (h)   ', (dateOnly.getTime() - dateTime.getTime()) / 3_600_000);
Runtime time zone Asia/Tokyo
Date only   2026-08-28T00:00:00.000Z
With time   2026-08-27T15:00:00.000Z
With offset 2026-08-27T15:00:00.000Z
Diff (h)    9

Just adding time shifts by 9 hours. And the last row shows: the shift size varies by runtime environment. On a UTC server, the diff is zero, so tests won't reproduce.

Workaround is simple. Always attach offset or time zone name to incoming datetime strings. If not possible, explicitly write in your code which interpretation to use. Passing omitted-form strings to new Date collides with an environment-dependent rule.

Boundaries move with time zones​

Computing "sales on August 28", the range cut differs by whose August 28.

Count the same 2 orders on Japan's day 1 and UTC's day 1.

-- Example is SQLite. created_at stored as UTC strings
CREATE TABLE orders (id INTEGER PRIMARY KEY, created_at TEXT NOT NULL);
INSERT INTO orders (id, created_at) VALUES
  (1, '2026-08-27 20:00:00'),  -- Japan time 8/28 5 AM
  (2, '2026-08-28 13:00:00');  -- Japan time 8/28 10 PM

-- Same "August 28" cut by two time zones. Column as-is; convert only boundaries
SELECT 'Japan day 1' AS range, count(*) AS count FROM orders
 WHERE created_at >= datetime('2026-08-28T00:00:00+09:00')
   AND created_at <  datetime('2026-08-29T00:00:00+09:00')
UNION ALL
SELECT 'UTC day 1', count(*) FROM orders
 WHERE created_at >= datetime('2026-08-28T00:00:00Z')
   AND created_at <  datetime('2026-08-29T00:00:00Z');
range        count
-----------  ----
Japan day 1  2
UTC day 1    1

Same date, different counts. The 5 AM order is still 8/27 in UTC. What's converted is only the boundary value side; created_at stays as-is in the condition. Wrapping the column in a function disables the index, so this form is key (Indexes).

How to write boundary values varies by product and column type. SQLite has no datetime type and compares strings lexically, so use datetime() to align to storage format. For a time-zone-aware column type, offset literals can be placed directly.

Nasty is columns with no time zone. How literal offsets are treated differs by product. PostgreSQL states: "In a value that has been determined to be timestamp without time zone, PostgreSQL will silently ignore any time zone indication" (PostgreSQL — Date/Time Types 8.5.1.3). You write an offset, the range doesn't shift a second, no error or warning.

Unchanging is "column as-is, convert the boundary" form.

Not writing offsets on boundary values makes interpretation depend on connection settings. As seen in Indexes, PostgreSQL interprets timezone-free input by session setting. Counting Japan's day 1 on a UTC connection puts morning 9 hours into the previous day and late night 9 hours into the current.

No error; the numbers just shift. And "roughly right" so it's usually caught at monthly reconciliation.

Deciding boundaries in 3 steps:

  1. Decide whose day 1 — end user's local, or business standard
  2. Write offset on boundary values — don't convert the column. Function on column disables index
  3. Close upper bound with "less than" — <= '23:59:59' misses sub-second values

Calendar arithmetic isn't straightforward​

"1 month later" and "1 day later" aren't fixed seconds.

// Add 1 month to month-end
const d = new Date(2026, 0, 31); // 2026-01-31 (local)
d.setMonth(d.getMonth() + 1);
console.log('1/31 + 1 month =', `${d.getFullYear()}/${d.getMonth() + 1}/${d.getDate()}`);

// "1 day later" as 24 hours shifts on DST day
const before = new Date('2026-03-08T00:00:00-05:00'); // NYC 3/8 0:00 (DST switch day)
const plus24h = new Date(before.getTime() + 24 * 60 * 60 * 1000);
const show = (d: Date) =>
  new Intl.DateTimeFormat('en-CA', {
    timeZone: 'America/New_York', dateStyle: 'short', timeStyle: 'short', hour12: false,
  }).format(d);
console.log('3/8 0:00 + 24h =', show(plus24h), '(local clock shifts by 1 hour)');
1/31 + 1 month = 2026/3/3
3/8 0:00 + 24h = 2026-03-09, 01:00 (local clock shifts by 1 hour)

Above: February has no 31, so the overflow of 3 days rolls to March. Below: DST start makes that day 23 hours, so adding 24 hours pushes the local clock 1 hour forward.

Treating "1 day" as 86,400 seconds is valid only for measuring instant differences. For "next day's same time" on the calendar, advance the date in local time then convert back to instant.

Leap seconds are the same family of trap, but caught by spec side. ECMAScript's time values are defined as "not considering UTC leap seconds" (ECMAScript — 21.4.1.1 Time Values and Time Range), so no value represents an instant within a positive leap second. 23:59:60 can't be constructed in JavaScript.

Display once at the end​

Summarizing everything into one rule: hold as instant internally; convert to local time once, just before displaying to a person.

// Show the same instant in three time zones
const instant = new Date('2026-08-28T00:30:00+09:00');
const show = (tz: string) =>
  new Intl.DateTimeFormat('ja-JP', {
    timeZone: tz, dateStyle: 'short', timeStyle: 'short', hour12: false,
  }).format(instant);

for (const tz of ['Asia/Tokyo', 'UTC', 'America/New_York']) {
  console.log(tz.padEnd(17), show(tz));
}
Asia/Tokyo        2026/08/28 0:30
UTC               2026/08/27 15:30
America/New_York  2026/08/27 11:30

The dates themselves differ. One instant is August 28 in Japan, August 27 in New York. "Group by date" produces different results depending on whether it runs before or after conversion.

Converting to local time midway locks subsequent computation to that region. Converting once at the exit avoids this accident.

Common misconceptions​

"Saving in UTC is safe." — Safe for records of what occurred. Not for future events. If time zone rules change, correct instants change. Store schedules as local time + time zone name.

"Saving +09:00 counts as saving the time zone." — It doesn't. Offset is the result of rules, not rules themselves — you can't recover which offset applies on a different date.

"Date-only and date-time strings both read as local time." — Different interpretations. Without offset, date-only is UTC; date-time is local.

"Setting the server to UTC eliminates time zone problems." — It doesn't. Users see local clocks, so daily aggregation boundaries and deadline judgments are decided in local time. Server settings don't solve display and boundary problems.

"Adding as 1 day = 86400 seconds works." — Only for measuring differences between instants. DST switch days are 23 or 25 hours, so for "next day" on the calendar, advance in local time.

Exercises​

Q1. Daily sales reports disagree slightly with accounting every day. App runs on a UTC server; date range cut by WHERE created_at >= '2026-08-28' AND created_at < '2026-08-29'. What's happening?

Answer and explanation

Aggregating UTC day 1, not Japan day 1.

Offset-free '2026-08-28' is interpreted by connection setting, so on a UTC server it's UTC 0:00 — which is Japan time 8/28 9 AM. So what's aggregated is Japan time 8/28 9:00 through 8/29 9:00, and two things happen simultaneously:

  • 8/28 sales from 0:00 to 9:00 go into the previous day's report
  • 8/29 sales from 0:00 to 9:00 sneak into the current day's report

They cancel across days, so totals roughly match. Only the daily breakdown shifts, hard to notice at monthly view.

Fix by writing the offset on the boundary values.

-- Example PostgreSQL. With timestamp with time zone column, offset literals placed directly
WHERE created_at >= '2026-08-28 00:00:00+09:00'
  AND created_at <  '2026-08-29 00:00:00+09:00'

Don't convert the column with AT TIME ZONE. Function on column disables that column's index (Indexes).

Check column type first. For types that drop the offset — PostgreSQL's timestamp without time zone, for one — this fix doesn't work; the range stays as before the "fix". The rule differs by product, so check in your environment.

Q2. An online meeting reservation for "3/15 10:00 (New York) next year" displayed as 9:00 on the day. Stored as UTC instant. Explain the cause.

Answer and explanation

Because you converted to UTC using the rule at reservation time and stored only that result.

March 15 in New York is already on DST (-04:00) under the current rules. Suppose that at reservation time you followed that rule, converted "3/15 10:00" to 14:00Z, and stored only the result. If DST is later abolished and the offset becomes -05:00 year-round, that same 14:00Z displays as local 9:00. What you stored is the result of applying a rule, so when the rule changes the display moves with it.

Storing future schedules as UTC instant alone breaks this way. What people promise is "10:00 on the local clock", not a specific UTC time.

Fix by storing both local time and time zone name. Holding 2027-03-15T10:00 and America/New_York lets you convert to instant with the latest rule at display and notification time. If you also hold the instant side, assume it's recomputed when rules update.

Q3. Code computing "30-day trial end date" occasionally reports 1-day shift. Implementation: new Date(start.getTime() + 30 * 24 * 60 * 60 * 1000). What do you suspect and fix?

Answer and explanation

Suspect the assumption "1 day = 24 hours".

This expression measures the instant 720 hours after the start. But users expect "30 days later on the calendar at the same time". In DST regions, the two don't match. Crossing the switch shifts the local clock by 1 hour; near midnight, the date changes.

"Occasional" report matches this hypothesis. It shifts only when crossing the switch.

Two fixes; which depends on what was promised:

  • Calendar 30 days promised — advance 30 in local time date, then convert to instant. Clock appearance is preserved
  • 720 hours use promised — current expression is correct; fix expectations instead

Also, whether end judgment is by date or instant comparison should be aligned. One in local date, other in instant, misaligns another day at boundaries.

Summary​

  • Datetimes split into instant, local time, duration. "2026-08-28 14:00" is local time, not an instant
  • Offset is a result; time zone is a rule. Storing +09:00 doesn't recover which region
  • Occurred: instant (UTC); scheduled: local time + time zone name. If rules change, correct instants change
  • Without offset, date-only is UTC; date-time is local. Shift varies by runtime, not reproducing on UTC servers
  • Day boundaries move with time zones. Write offset on boundary values; don't convert the column
  • Calendar arithmetic isn't fixed seconds. Month-end addition overflows; DST switch days aren't 24 hours
  • Convert once at the exit. Converting midway locks subsequent computation to that region
Related references