bwi

Posted on Jan 22

Instant vs Local – When UTC Helps and When It Hurts

#architecture #database #softwareengineering #systemdesign

Part 4 of 8 in the series Time in Software, Done Right

"Just store everything in UTC."

You've probably heard this advice. It sounds simple, clean, universal. And for some use cases, it's exactly right.

But for others, it's a trap that will corrupt your data in ways you won't notice until it's too late.

This article explains when UTC is the right choice, when it destroys meaning, and what model actually works for human-facing times.

Two Fundamentally Different Questions

When you're storing a time, you need to ask yourself which question you're answering:

Question A: "What physical moment did this happen?"

Question B: "What did the human mean when they said '10:00 in Vienna'?"

These are not the same question. They need different storage strategies.

When UTC Is Perfect

UTC (or Instant) is the right choice when you're recording physical moments — things that happened at a specific point in time, independent of any human's calendar.

Use UTC for:

Log entries — "Request received at 2026-01-21T13:05:12Z"
Audit trails — "User clicked 'Submit' at this instant"
Token expiry — "This token is valid for 3600 seconds from now"
Event sourcing — "This event occurred at this physical moment"
Timestamps for ordering — "Did A happen before or after B?"
Durations and intervals — "How long did this take?"

In all these cases, you don't care about calendars, timezones, or what the clock on someone's wall showed. You care about the moment itself.

UTC is perfect for this. It's unambiguous, comparable, and never changes.

When UTC Destroys Meaning

UTC is the wrong choice when you're storing human intent — times that exist because a person chose them, in a specific calendar context.

Don't use UTC alone for:

Meetings — "Team standup at 10:00 Vienna time"
Appointments — "Doctor's appointment at 14:30"
Deadlines — "Submit by June 5th, 23:59 Vienna"
Opening hours — "Store opens at 09:00"
Recurring events — "Every Monday at 10:00"
Birthdays and anniversaries — "Party on March 15th at 19:00"

Why? Because these times have meaning beyond the instant.

This isn't obvious at first — many systems start this way and run into problems later.

The Problem with "Just Convert to UTC"

Let's say a user in Vienna schedules a meeting for June 5th at 10:00.

If you convert immediately to UTC and store only that:

stored: 2026-06-05T08:00:00Z

You've lost information. You no longer know:

That the user meant "10:00 in Vienna"
That Vienna was in CEST (Central European Summer Time) at that moment
What should happen if timezone rules change

Now imagine: the EU abolishes Daylight Saving Time in 2027. Vienna stays on permanent standard time (UTC+1 instead of UTC+2 in summer).

Your stored UTC value 08:00Z now displays as 09:00 Vienna time — not 10:00!

The user said "10:00". The meeting should still be at 10:00. But you stored the math, not the meaning.

Why Offsets Don't Help

"But I stored 2026-06-05T10:00:00+02:00 — that includes the offset!"

Yes, but an offset is a snapshot, not a meaning.

+02:00 could be:

Vienna in summer
Berlin in summer
Cairo
Johannesburg
Kaliningrad

You can't tell which one. And more importantly: if the rules change, you can't recalculate.

An offset tells you what the clock showed at the moment of storage. It doesn't tell you what the user meant, or what the correct time should be in the future.

The Correct Model: Store Intent, Derive Math

Here's the model that actually works:

Source of truth:    local + timeZoneId
Derived value:      instantUtc

For a meeting at 10:00 Vienna:

local:        2026-06-05T10:00
timeZoneId:   Europe/Vienna
instantUtc:   2026-06-05T08:00:00Z  (derived)

What you store as truth:

The local datetime the user chose (10:00)
The IANA timezone (Europe/Vienna)

What you derive for queries:

The UTC instant (computed from local + zone)

Why IANA Timezones Matter

An IANA timezone like Europe/Vienna is not just an offset. It's a ruleset:

Historical offsets (what was the offset in 1980?)
Current offset
DST transitions (when does the clock change?)
Future rules (as currently known)
Political changes (when a country changes its timezone policy)

When you store Europe/Vienna, you're saying: "Apply whatever rules Vienna has — past, present, or future."

This is why the model survives rule changes.

What Happens When Rules Change

Timezone rules change more often than you'd think:

Russia abolished DST in 2011
Mexico abolished DST in 2022
EU has been discussing abolishing DST for years
Morocco changes its offset for Ramadan every year
Egypt — the chaos champion: introduced DST in 1940, suspended it in 2011, reintroduced it in 2014, suspended it during Ramadan that same year, abolished it in 2015, restored it in 2023

With the correct model:

For past events:

The IANA database contains historical rules
Your instantUtc was correct at the time and stays correct
Recalculating gives the same result (safe, idempotent)

For future events:

When rules change, you recalculate instantUtc
The human intent (10:00 Vienna) stays the same
The physical moment shifts to match the new rules

When Do You Actually Recalculate?

So instantUtc "can always be recalculated" — but when does that actually happen?

Trigger: Your IANA timezone database gets updated.

The IANA maintains the tz database, which gets updated several times a year. When a country announces a DST change or offset shift, the database is updated, and eventually that update reaches your system:

Your OS ships a tzdata update
NodaTime releases a new tzdb version
Your runtime (JVM, .NET, Node) updates its bundled data

What to do when this happens:

Past events: Nothing. Recalculating them is safe — the result will be identical because historical rules don't change.
Future events: Recalculate instantUtc for any event whose timezone was affected.

How to implement this:

Batch job: When you update your tz data, run a migration that recalculates instantUtc for future events in affected zones.
Lazy recalculation: Recompute instantUtc on read, and store the tz database version used. If it's stale, recalculate.
Hybrid: Batch for known changes, lazy as a safety net.

Most applications can get away with a simple batch job that runs after tz updates. If you're caching instantUtc in a search index or external system, remember to update those too.

The good news: this is rare (a few times a year, usually affecting only a handful of zones), and if you've stored local + timeZoneId, you have everything you need to recalculate correctly.

The Two Query Patterns

This model supports both ways of querying time:

Pattern A: "What's on my calendar on June 5th in Vienna?"

Query by local datetime + timezone:

WHERE time_zone_id = 'Europe/Vienna'
  AND local_start >= '2026-06-05'
  AND local_start <  '2026-06-06'

This finds everything on that calendar day, regardless of the global instant.

Pattern B: "What's happening globally at this moment?"

Query by instant:

WHERE instant_utc = '2026-06-05T08:00:00Z'

This finds everything happening at that physical moment, regardless of local calendars.

Both queries are valid. Both are useful. The model supports both.

How Many Columns Do You Actually Need?

Not every time field needs the same treatment. It depends on what you're storing:

Instant-only fields (logs, audits, occurredAt):
→ 1 column — timestamp with time zone or Instant

Date-only fields (birthday, holiday):
→ 1 column — date or LocalDate

Human-scheduled time (appointment, meeting, deadline):
→ 2 columns minimum:

local_start (timestamp without time zone)
time_zone_id (text — IANA zone)

Optional 3rd column: instant_utc as a cached/derived value — only if you need efficient global queries.

A Practical Example

For one human-facing timestamp (like an appointment start), the robust representation is:

-- Example using PostgreSQL types (concepts apply to any database)
local_start    timestamp without time zone NOT NULL,
time_zone_id   text NOT NULL,  -- IANA zone, e.g. 'Europe/Vienna'

-- Optional: cached for fast global queries/sorting
instant_utc    timestamp with time zone

The exact types vary by database (datetime in SQL Server, DATETIME in MySQL, etc.), but the pattern is the same: local datetime + timezone ID + optional UTC instant.

When do you need instant_utc?

Indexing and range queries across timezones
Sorting events globally
"What's happening right now?" queries
Avoiding recalculation in hot paths

If you never query globally, you can skip it and compute on read.

In practice, many teams wrap these two (or three) values into:

An app-level value object (ZonedDateTime, a custom DTO)
A composite database type
A domain type with constraints

The logical model stays the same — you're just packaging it.

The Mantra

Store intent, not math.

local + timeZoneId = what the human meant
instantUtc = what the computer needs for calculations

The human intent is the source of truth. The UTC instant is derived, cached, and can always be recalculated.

If you store only UTC, you've thrown away the intent. You can never get it back.

Key Takeaways

UTC is perfect for physical moments (logs, audits, tokens)
UTC alone is wrong for human intent (meetings, deadlines, appointments)
Offsets are snapshots, not meaning — they can't survive rule changes
The correct model: local + IANA timezone as truth, UTC instant as derived
IANA timezones contain rules, not just offsets — they handle past and future
When rules change: past events stay correct, future events get recalculated
Store intent, not math

Next up: Global Events, Local Events, and Recurring Rules — when "everyone at 10:00" means completely different things.

DEV Community