Your Timestamp is a Lie

What a train ticket taught me about storing time in databases

I was booking a train from New York to Chicago when it hit me why timestamp types in Postgres are so confusing. The ticket showed:

• Departs: 8:00 AM EST
• Arrives: 7:30 PM CST
• Duration: 11 hours 30 minutes

Three different ways of talking about time, all on the same ticket. And each one needs to be stored differently in a database.

The Question Nobody Asks First

Both TIMESTAMP and TIMESTAMPTZ in Postgres take up exactly 8 bytes with the same microsecond precision. So why have two types at all?

Because “what time is it?” depends entirely on what you’re trying to tell someone.

When I board that train in New York, I need to know it leaves at 8:00 AM Eastern. That’s the number on the station clock I need to match. When my friend picks me up in Chicago, she needs to know I arrive at 7:30 PM Central—that’s the number on her clock. And if I’m trying to figure out whether I’ll have time to read my book, I need to know it’s an 11-and-a-half-hour journey.

Same train. Same journey. Three completely different representations of time.

What TIMESTAMPTZ Actually Does

Here’s the trick with TIMESTAMPTZ—and it’s not what most people think. It doesn’t store the timezone. The name is misleading.

What it does is convert whatever time you give it to UTC before storing it, then converts it back to your session’s timezone when you read it. The “TZ” part isn’t about storage, it’s about conversion support.

Say you’re storing that train departure. Someone in Tokyo queries your database and sees the departure in JST. Someone in London sees it in GMT. Everyone’s looking at the same absolute moment, just expressed in their configured timezone. This is perfect for recording events: “when did this payment process?” or “when did this API request happen?”

But what about that train ticket? You don’t want the departure time to change just because someone queries it from a different timezone. The train leaves at 8:00 AM Eastern, period. That’s not an absolute moment in time—it’s a promise about what the clock in Grand Central will say.

Storing What You Actually Mean

For that train journey, you need to store different things for different purposes:

• The absolute moments (departs_at and arrives_at as TIMESTAMPTZ)
• The display context (origin_timezone and destination_timezone as text)
• The duration (an INTERVAL between the two moments)

Now your application can do what the train ticket does: show “Departs 8:00 AM EST” by converting the absolute moment to the origin timezone, show “Arrives 7:30 PM CST” by converting to the destination timezone, and show “Duration: 11h 30m” directly from the interval.

The person booking the ticket from Tokyo sees the same local times at each station. That’s what they need to know.

Why Your Flight Tracking App Got It Wrong

Ever notice how some flight tracking apps show your timezone during the flight? Like you’re over the Atlantic and it says “Current time: 4:32 PM GMT.” Who cares? You’re not in Greenwich, you’re at 38,000 feet somewhere over the ocean.

What you actually want to see:

• Time elapsed since takeoff
• Time remaining to destination
• What time it’ll be there when you land

None of those are timezone conversions. The first two are intervals—durations, not moments. The last one is a timezone conversion, but to a specific place, not “your current timezone.”

See that? Two interval calculations (NOW() - actual_departure and estimated_arrival - NOW()), one timezone conversion to a specific place (AT TIME ZONE destination_timezone). Your current timezone doesn’t enter into it.

When Wall-Clock Time Is What You Actually Need

Hotels don’t care about absolute moments in time. They care about clock readings at their location.

“Check-in is after 3:00 PM” doesn’t mean “check-in is 15 hours after midnight UTC.” It means “whenever the clock in our lobby says 3:00 PM, you can check in.” If your servers are in Virginia but the hotel is in Paris, you still want that rule to trigger at 3:00 PM Paris time.

The TIME type (without date or timezone) represents exactly this: “a reading on a clock.” Pair it with a timezone text field (“Europe/Paris”), and you can enforce wall-clock policies regardless of where your servers live. But you’ll also want TIMESTAMPTZ columns for when specific guests actually check in and out—those are absolute moments your backend needs to track.

The Calendar Problem

I have a recurring reminder set for 9:00 AM: “Review daily priorities.” I want that reminder at 9:00 AM wherever I am. If I’m traveling, it should still fire at 9:00 AM local time.

But I also have a calendar event: “Team Standup at 10:00 AM EST.” My teammate in Berlin needs to see “4:00 PM CET” for that same event. Same meeting, different display times, because this one is an absolute moment we’re all joining.

Two different types of events, two different storage strategies. The meeting gets a TIMESTAMPTZ. The reminder gets a TIME plus my current timezone setting. Avoid trying to force both into the same field.

The Stuff That Breaks in Production

Even with the right types, precision can bite you. Postgres stores microseconds: 10:00:00.123456. JavaScript’s Date object uses milliseconds: 10:00:00.123.

So this query might mysteriously return no rows:

SELECT * FROM orders WHERE created_at = '2026-01-15 10:00:00.123';

The database has 10:00:00.123456 and your code passes 10:00:00.123. Depending on how your driver handles it, those might not match.

Don’t use exact equality for timestamps. Use range queries, or—better—don’t look up records by their creation timestamp at all. Use a proper unique constraint or idempotency key.

Practical Rules

Default to TIMESTAMPTZ. When in doubt, use TIMESTAMPTZ. It handles multi-region deployments, daylight saving time, and future timezone changes automatically. It’s the same storage size as TIMESTAMP, so there’s no penalty.

Store context separately. If you need to show “Departs 8:00 AM EST” alongside the actual moment, store both the TIMESTAMPTZ and the origin_timezone as separate columns. Don’t try to encode everything into one field.

Think about intervals. A lot of time-related requirements are actually about duration, not moments. “How long has this been pending?” “When will this expire?” Use INTERVAL operations, not timezone conversions.

Run everything in UTC. Your servers should be set to UTC. Your database sessions should default to UTC. Only convert to local timezones when displaying to users, and only when you know which timezone matters.

Require timezone info from clients. If a client sends 2026-01-15T10:00:00 without an offset, reject it. Require ISO-8601 format with either Z or an explicit offset like -05:00. Don’t guess.

Enforcing Good Defaults

If TIMESTAMPTZ is your default (and it should be), consider enforcing it at the database level. A trigger that rejects TIMESTAMP WITHOUT TIME ZONE columns sounds extreme, but catching “forgot to add TZ” at schema creation time is better than debugging it six months later when someone adds a new table and forgets.

What That Train Ticket Taught Me

Time in databases isn’t hard because timestamps are complicated. It’s hard because we’re usually storing multiple concerns in one field, or not thinking about what we’re actually trying to show users.

That train ticket had it right: departure time in the origin timezone, arrival time in the destination timezone, and duration as a separate thing entirely. Three different pieces of information, each meaningful in its own way.

Your database can do the same thing. Store the absolute moments as TIMESTAMPTZ. Store the display context (timezones, locations) as separate columns. Use INTERVAL types for durations. Let Postgres do the conversions when you need them, but be explicit about which timezone matters for which purpose.

Most of the time, that means TIMESTAMPTZ and UTC everywhere, with timezone conversions only at display time. But when you need wall-clock times or recurring schedules, TIMESTAMP or TIME types exist for exactly that reason.

The key is knowing what question you’re trying to answer: “When did this happen?” vs. “What time should I be there?” vs. “How long will this take?” They’re all different questions about time, and they often need different storage strategies.

Think about what your users need to see. Then store the data that lets you show them exactly that.

Resources

• PostgreSQL Date/Time Types Documentation
• PostgreSQL Timestamp Best Practices
• ISO 8601 Date and Time Format
• Time Zone Database (IANA)
• Dealing with Timestamps in Distributed Systems