Datetime Is Tech Debt You Didn't Know You Had

Why This Matters

#

Almost every developer has a datetime story. An appointment showing up an hour late. A report aggregating the wrong day’s data. A notification firing at 3am. It works fine in development, ships, and then a user in a different timezone files a bug you can’t immediately reproduce.

A 2025 empirical study of real-world bugs in open-source software found that 53.6% of datetime bugs are timezone-related, and 27.8% stem specifically from mixing naive and timezone-aware datetime objects incorrectly. These are the result of treating datetime as a trivial detail.

Datetime is one of the most common forms of silent tech debt. It’s easy to defer because it appears to work until it doesn’t. By the time it fails, it’s in production for a user in a country you didn’t test for. By then, you’re stuck refactoring entire columns in a live production database.

This post is the reference I wish I’d had earlier — terminology, mental models, and the decisions that actually matter. Treat it as a cheat sheet.

The Terminology (Get This Right First)

#

Before anything else, let’s align on words. These get conflated constantly.

UTC: The One Ring

#

UTC is the reference point everything else orbits. It doesn’t observe DST. It doesn’t change. When you know a moment in UTC, you can derive the correct wall clock time anywhere in the world by applying the local offset.

flowchart TD
    UTC["🌍 09:00 UTC"]
    UTC --> Dubai["Dubai +04:00\n→ 13:00"]
    UTC --> London["London +01:00 (BST)\n→ 10:00"]
    UTC --> Toronto["Toronto −04:00 (EDT)\n→ 05:00"]
    UTC --> Tokyo["Tokyo +09:00\n→ 18:00"]

    classDef center fill:#1e3a5f,stroke:#4a9eff,color:#e0f0ff
    classDef tz fill:#1a2e1a,stroke:#4caf50,color:#b8f0b8

    class UTC center
    class Dubai,London,Toronto,Tokyo tz

Store in UTC. Display in local. That’s the whole principle. Everything else is implementation detail.

ISO 8601: The Transfer Format

#

When moving a datetime between systems — from backend to frontend, from database to API, from one language to another — you need a string format both sides understand. That format is ISO 8601.

2024-06-01T09:00:00          ← naive (no offset) — avoid this
2024-06-01T09:00:00Z         ← UTC (Z = zero offset)
2024-06-01T09:00:00+00:00    ← UTC (explicit offset)
2024-06-01T13:00:00+04:00    ← Dubai local time
2024-06-01T05:00:00−04:00    ← Toronto local time

The last four all represent the same moment in time. A system that parses ISO 8601 correctly will handle all of them identically — converting to UTC internally and displaying in the user’s local timezone.

The naive format (no offset) is the one to avoid. It looks like a datetime but carries no timezone information. Every system that receives it has to guess — and they won’t all guess the same thing.

How Postgres Handles This

#

Postgres has two datetime types that look similar but behave completely differently:

flowchart TD
    A["Send: '2024-06-01T13:00:00+04:00'\n(Dubai 1pm = 9am UTC)"]

    A -->|timestamp column| B["Strips offset\nStores: 13:00:00\nOffset lost forever ❌"]
    A -->|timestamptz column| C["Converts to UTC\nStores: 09:00:00 UTC\nOffset preserved ✅"]

    B --> D["Returns: '2024-06-01T13:00:00'\nNo offset — ambiguous"]
    C --> E["Returns: '2024-06-01T09:00:00+00:00'\nUTC with offset — unambiguous"]

    classDef input fill:#1e3a5f,stroke:#4a9eff,color:#e0f0ff
    classDef bad fill:#3b1a1a,stroke:#f87171,color:#fecaca
    classDef good fill:#1a2e1a,stroke:#4caf50,color:#b8f0b8
    classDef out fill:#2a2a3b,stroke:#a78bfa,color:#ede9fe

    class A input
    class B bad
    class C good
    class D,E out

Default to timestamptz for every datetime column. The overhead is negligible. timestamp is appropriate only when you can guarantee the writer and reader will always be in the same timezone — a constraint that rarely holds as products grow.

Don’t Split Date and Time Into Separate Columns

#

This one is counterintuitive but important.

A common pattern is storing date and time separately:

appointment_date DATE,   -- "2024-06-01"
appointment_time TIME,   -- "09:00:00"

It seems clean. But it breaks down across timezones.

The problem: a “day” is not the same moment everywhere.

If a client books at 09:00 Dubai time on June 1st, that’s 05:00 UTC on June 1st. But for a user in Toronto viewing the same record, it’s 01:00 on June 1st — still June 1st, fine. But what if the appointment was at 02:00 Dubai time? That’s 22:00 UTC on May 31st. The date changes depending on where you’re standing.

When you store date and time as separate fields, you lose the ability to reconstruct the correct moment in time without knowing the business’s timezone — information that isn’t in those columns.

flowchart TD
    A["Appointment: June 1st, 02:00 Dubai time"]
    A --> B["date = '2024-06-01'\ntime = '02:00:00'"]
    B --> C{"What is this in UTC?"}
    C -->|"Need to know timezone"| D["Asia/Dubai (+04:00)\n→ May 31st 22:00 UTC"]
    C -->|"Assumed UTC"| E["June 1st 02:00 UTC ❌\nWrong by 4 hours"]

    B --> F["Single timestamptz column\n'2024-06-01T02:00:00+04:00'\n→ Stored as May 31 22:00 UTC ✅"]

    classDef input fill:#1e3a5f,stroke:#4a9eff,color:#e0f0ff
    classDef split fill:#3b1a1a,stroke:#f87171,color:#fecaca
    classDef correct fill:#1a2e1a,stroke:#4caf50,color:#b8f0b8
    classDef decision fill:#2a2a3b,stroke:#a78bfa,color:#ede9fe
    classDef wrong fill:#3b1a1a,stroke:#f87171,color:#fecaca

    class A input
    class B,C split
    class D,F correct
    class E wrong

Use a single timestamptz column. It stores the full moment — date, time, and UTC equivalent — atomically. You can always extract the date or time for display on the client.

When date Alone Is Correct

#

There are cases where a plain date column is genuinely right: when the value is inherently timezone-independent.

Birthdate is the canonical example. If someone was born on June 1st, that’s true regardless of what timezone you’re in when you ask. It’s a calendar fact, not a moment in time. Storing it as timestamptz would actually introduce a problem — you’d need to decide which timezone to use, and converting it could shift the date.

Same for:

• Public holidays (2024-12-25 is Christmas everywhere)
• Contract effective dates (2024-07-01 a policy takes effect)
• Expiry dates on a license or document

The test: “Does the answer change depending on where you are when you ask?” If no — use date. If yes — use timestamptz.

The Cheat Sheet

#

flowchart TD
    Q1{"Is this a calendar fact\nindependent of timezone?\ne.g. birthdate, holiday"}
    Q1 -->|Yes| date["Use DATE column\nStore: '2024-06-01'"]
    Q1 -->|No| Q2{"Do you need sub-day precision?"}
    Q2 -->|No| dateonly["Use TIMESTAMPTZ\nStore the moment, extract local date on read\ne.g. which day did this land in the user's tz?"]
    Q2 -->|Yes| tstz["Use TIMESTAMPTZ column\nSend: ISO 8601 with offset\nPostgres stores UTC\nClient calls .toLocal() on read"]

    classDef q fill:#2a2a3b,stroke:#a78bfa,color:#ede9fe
    classDef good fill:#1a2e1a,stroke:#4caf50,color:#b8f0b8

    class Q1,Q2 q
    class date,tstz,dateonly good

Write path (always):

• Send an offset-aware ISO 8601 string: 2024-06-01T13:00:00+04:00
• Never send a naive string to a timestamptz column — Postgres will assume UTC

Read path (always):

• For timestamptz: the string comes back with +00:00 — parse it, then convert to the user’s local timezone before display
• For timestamp / date: the string comes back with no offset — treat as-is, no conversion

Never:

• Store datetime as a plain string
• Split a moment in time across separate date + time columns
• Assume the server timezone matches the user timezone
• Skip .toLocal() (or equivalent) on display

One Last Thing: Offset vs Timezone

#

These are not the same, and the distinction matters if your product operates across DST boundaries.

+04:00 is an offset — a fixed number. Asia/Dubai is a timezone — a named ruleset that includes DST transitions.

Dubai doesn’t observe DST, so Asia/Dubai is always +04:00. But America/Toronto is −05:00 in winter and −04:00 in summer. If you hardcode the offset, you’ll be wrong half the year.

Store and reason in named IANA timezones (Asia/Dubai, Europe/Berlin, America/Toronto). Use the offset only at the moment of conversion, derived from the IANA name at that specific point in time.

Datetime isn’t hard. But it requires deliberate thought — the kind most developers postpone until users are in two countries and support tickets start rolling in. Get it right from the start and it stays invisible, which is exactly where infrastructure should be.