caduh

Time, Timezones, and Timestamps — the least boring, most dangerous part of your stack

6 min read

UTC vs local time, offsets vs zones, DST edge cases, ISO 8601/RFC 3339, DB column types, and sane patterns in JS/Python/SQL. How to store, compare, schedule, and display time without pain.

TL;DR

  • Store instants in UTC. Keep the user’s IANA time zone (America/New_York) as a separate column/claim.
  • Exchange timestamps in ISO 8601/RFC 3339 with a Z (UTC) or explicit offset (+02:00). Never send ambiguous local strings.
  • Use timezone‑aware APIs; do calendar math in a zone, do durations in UTC/monotonic.
  • DST exists: some local times repeat (fall‑back) or don’t exist (spring‑forward). Schedule with a zone‑aware scheduler, not by adding 24h.
  • Databases: prefer Postgres timestamptz for instants; in MySQL use TIMESTAMP (UTC semantics) or DATETIME for literal calendar values; utf‑8mb4 not relevant here 😅.
  • Frontend: format with Intl.DateTimeFormat (JS) or libraries; avoid raw Date gymnastics. For duration measurement, use monotonic clocks.

1) Core terms (speak precisely)

  • Instant: a point on the timeline (e.g., “2025‑10‑01T13:37:00Z”).
  • Local date‑time: same number wall‑clock (no offset) — ambiguous without a zone.
  • Offset: hours/minutes from UTC at that moment (e.g., +02:00).
  • Time zone (IANA): rules over time (DST, historical changes), like Europe/Berlin. Do not use abbreviations (PST/CET).
  • Duration: exact amount of elapsed time (e.g., 45 minutes).
  • Period: calendar math (e.g., add “1 month”) that depends on a zone (29/30/31 days).

2) Golden rules you can live by

  1. Persist instants in UTC (TIMESTAMP WITH TIME ZONE in Postgres; TIMESTAMP in MySQL).
  2. Keep user zone separately (profile, org setting). Format for display at the edge/client.
  3. When scheduling “every day at 09:00 in X”, store the zone + wall time; let a zone‑aware library compute next fire.
  4. Never add 24h to a local time to get “tomorrow 09:00” — do calendar add in that zone.
  5. For performance timings/retries/timeouts, use monotonic clocks, not wall time.

3) ISO 8601 / RFC 3339 — what to send on the wire

Examples that are safe:

2025-10-01T13:37:00Z           # Z = UTC
2025-10-01T15:37:00+02:00      # explicit offset
2025-10-01                      # a date only (no time)

Avoid:

10/01/2025 1:37 PM             # locale-specific & ambiguous
2025-10-01T13:37:00            # missing offset/zone (ambiguous)

4) Databases — choose the right column

Postgres

  • Use timestamptz (a.k.a. timestamp with time zone) for instants. It stores UTC and converts on formatting.
  • Use timestamp without time zone only for pure local notions (e.g., “store opens at 09:00” with a separate zone field).
-- Instants
CREATE TABLE events (
  id bigserial PRIMARY KEY,
  at timestamptz NOT NULL DEFAULT now(),
  user_tz text NOT NULL DEFAULT 'UTC'  -- IANA name
);

-- Convert for reporting
SELECT at AT TIME ZONE 'America/New_York' AS local_time FROM events;
-- (Note: above returns a *timestamp without time zone* representing local wall time)

MySQL

  • TIMESTAMP is stored UTC and converted on session time zone; range is limited (1970–2038 in older versions).
  • DATETIME stores literal numbers (no zone conversion). Use for local calendar values.
CREATE TABLE sessions (
  id BIGINT PRIMARY KEY,
  started_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, -- instant
  local_start DATETIME,  -- e.g., 09:00 of user's day
  user_tz VARCHAR(64) NOT NULL
);

SQLite

  • No native time zone type; store ISO strings or Unix seconds and handle zones in the app.

5) Practical code (JS, Python, SQL)

JavaScript (browser/Node)

// Serialize now in UTC (RFC 3339)
const nowIso = new Date().toISOString(); // "2025-10-01T11:22:33.456Z"

// Format for a user zone with Intl
const fmt = new Intl.DateTimeFormat('en-US', { dateStyle: 'medium', timeStyle: 'short', timeZone: 'America/New_York' });
fmt.format(new Date(nowIso)); // "Oct 1, 2025, 7:22 AM"

// Measure a duration (monotonic)
const t0 = performance.now();
// ... do work ...
const ms = performance.now() - t0;

If available, the Temporal API (or polyfill) gives first‑class ZonedDateTime and safer math.

// Example with Temporal polyfill
// import { Temporal } from "@js-temporal/polyfill";
const instant = Temporal.Now.instant();
const ny = instant.toZonedDateTimeISO("America/New_York");
const tomorrowNine = ny.with({ hour: 9, minute: 0, second: 0 }).add({ days: 1 }).toInstant();

Python 3.9+

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

now_utc = datetime.now(timezone.utc)        # aware
ny = now_utc.astimezone(ZoneInfo("America/New_York"))

# Serialize / parse safely
iso = now_utc.isoformat().replace("+00:00", "Z")

# Schedule: next 09:00 in user's zone (handles DST via zone rules)
def next_run_9am(tz: str, ref: datetime) -> datetime:
    z = ZoneInfo(tz)
    local = ref.astimezone(z)
    candidate = local.replace(hour=9, minute=0, second=0, microsecond=0)
    if candidate <= local:
        # add a calendar day in the zone, not 24h of UTC
        candidate = (candidate + timedelta(days=1)).astimezone(z)
    # Convert back to an instant
    return candidate.astimezone(timezone.utc)

Postgres snippets

-- Parse/format
SELECT now() AT TIME ZONE 'UTC' AS utc_now;           -- timestamp without time zone (wall clock)
SELECT (now() AT TIME ZONE 'America/Los_Angeles');    -- local wall time

-- Truncate by day in a zone (bucket analytics by user zone)
SELECT date_trunc('day', at AT TIME ZONE 'America/New_York') AS ny_day, count(*)
FROM events
GROUP BY 1
ORDER BY 1;

6) DST edge cases & scheduling safely

  • Spring forward: some times don’t exist (e.g., 2025‑03‑09 02:30 in America/Los_Angeles).
  • Fall back: some times repeat; 01:30 happens twice — use the fold or offset to disambiguate (Python fold attribute; Temporal keeps offset).
  • Rule: store (zone, local time) for schedules; compute next fire using a zone‑aware lib; store the resulting instant as the trigger time.

Don’t do this

// ❌ Adds 24h; breaks across DST boundaries
next = new Date(current.getTime() + 24*60*60*1000);

Do this

// ✅ Add a calendar day in the user's zone, then convert to an instant
const tz = 'America/New_York';
const nextInstant = Temporal.Now.zonedDateTimeISO(tz).with({ hour: 9, minute: 0 }).add({ days: 1 }).toInstant();

7) Durations, timeouts, and retries

  • Measure with monotonic clocks (performance.now(), time.monotonic(), Go’s time.Since uses monotonic).
  • Store durations as numbers + units (e.g., milliseconds) or ISO 8601 durations (PT5M). Don’t mix wall‑time and durations.

8) Unix time, precision, and big numbers

  • Unix epoch = seconds since 1970‑01‑01T00:00:00Z (ignores leap seconds).
  • JS Date stores milliseconds; many APIs use seconds. Be explicit (sec ↔ ms).
  • JS Number is 53‑bit integer safe: epoch milliseconds are safe for many centuries, but micro/nanoseconds need BigInt or strings.
// Seconds ↔ milliseconds
const seconds = Math.floor(Date.now() / 1000);
const millis = seconds * 1000;

// Nanoseconds via BigInt (Node)
const ns = process.hrtime.bigint();

9) Common pitfalls & fast fixes

| Pitfall | Why it bites | Fix | |---|---|---| | Storing local time without zone | Ambiguous, wrong after DST/relocation | Store UTC instant + user IANA zone | | Missing offset in strings | Parsing ambiguity | Send RFC 3339 (Z/±hh:mm) | | Adding 24h for “tomorrow” | Breaks across DST | Do calendar add in zone | | Doing date math on strings | Locale surprises | Parse → operate in typed APIs (zone‑aware) | | JS Date only | Footguns on parsing/formatting | Use Intl; consider Temporal/library | | MySQL DATETIME for instants | No zone semantics | Use TIMESTAMP (UTC) for instants | | Using time for perf timing | Clock jumps | Use monotonic timers | | Abbrev zones (PST/CET) | Not unique | Use IANA zone IDs |

Quick checklist

  • [ ] Persist UTC instants; save user IANA zone separately.
  • [ ] Serialize timestamps as ISO 8601/RFC 3339 with Z/offset.
  • [ ] Format for users with Intl/ICU using their zone.
  • [ ] Schedule by (zone, wall time); compute next fire via zone rules.
  • [ ] Use monotonic clocks for durations/timeouts.
  • [ ] In Postgres: timestamptz for instants; timestamp for local values.
  • [ ] Document seconds vs milliseconds in APIs.

One‑minute adoption plan

  1. Audit schema: convert instant columns to UTC (timestamptz/TIMESTAMP); add a user_zone field where needed.
  2. Standardize on RFC 3339 on the wire; reject timestamps without offset/Z.
  3. Replace “+24h” logic with zone‑aware calendar math.
  4. Use Intl.DateTimeFormat (or Temporal) to render; add monotonic timing for performance.
  5. Add tests for DST transitions (skipped/repeated hours) in your top 3 user zones.
← Back to all posts