Should I Store a Date as a Timestamp or an ISO String?

A code review stalls over one column. One developer stored created_at as a BIGINT — a Unix timestamp integer. Another insisted on a PostgreSQL timestamptz that the driver serializes back as an ISO string. Meanwhile the third-party API you integrate with hands you "2026-07-15T22:00:00Z", and the JWT you issue carries exp as a ten-digit integer. Which one is “right”? The argument almost always jams in the same place — integer or string — and then goes nowhere.

Here’s the thing: integer or string is the least important and last question in a sequence. There’s a question before it that, if you answer it wrong, makes the integer-versus-string choice irrelevant — both paths crash anyway. That question is: what kind of value is this? An instant, a calendar date, a future local commitment, or a duration? Answer that first, then ask how to store it. Because once you’ve established it’s an instant, an integer timestamp and an ISO string with an offset are two notations for the same information — neither is more “correct,” and you’re choosing on engineering trade-offs, not correctness. The thing that actually manufactures bugs is a third answer nobody chose on purpose but that keeps sneaking in: a local time with no zone.

This is the fourth and closing piece of the date-and-time cluster. The first three covered the number itself (why a Unix timestamp is wrong), the pile of names around it (the difference between UTC, GMT, and ISO 8601), and the calendar-date boundary (why a date shifts by a day). This one lands them all on a very practical question: when you actually write a time into a database or drop it into an API response, what do you write down?

Ask “what is it” before “what shape does it take”

Integer or string is a question of shape. The more important question before it is one of kind. Nearly every storage-layer time bug picks the wrong kind at this step, and no amount of integer-versus-string cleverness recovers from it afterward. There are four date-shaped kinds of value, and each wants a different thing:

• An instant — created_at, a log time, the moment a payment cleared. An absolute point on the timeline, the same for everyone. Store it as an explicitly anchored instant: a Unix timestamp, or an ISO string with an offset. Only for this kind are those two the same information in two notations.
• A calendar date — a birthday, an invoice date, a due date, an “all-day” event. It has no time and no zone (May 1st, 1990 is the same May 1st in Tokyo and Chicago). Store it in a DATE type, or just the string "1990-05-01" — and never launder it through an instant type, or you’ll shift it a day early with your own hands.
• A future local commitment — “9 AM on the 3rd in Berlin, next year,” a weekly meeting. The offset that actually applies then can change when that zone’s DST rules do, so store the local time plus the IANA zone name (Europe/Berlin), not a frozen offset. If it recurs, store the recurrence rule too (weekly, monthly — an RFC 5545 RRULE) beside the local time and zone.
• A duration or interval — a cache TTL, a timeout, the “30 days” in “expires in 30 days.” That’s a quantity, not a moment — store an integer (seconds or milliseconds, with the unit in the column name or docs), or, if it’s really an expiry moment, store that expiry instant. Mind one distinction: a fixed duration (30 × 24 hours) is a count of seconds, but a calendar period (“one month,” “30 calendar days”) carries calendar semantics and must not be flattened to a fixed number of seconds — it can cross a DST boundary or a short month.

Put differently: the clean “two notations, one value” equivalence holds only for the instant. The other three kinds have representation choices too — a DATE versus a string, an integer versus an ISO 8601 duration like P30D, a structured object for a recurring event — but those forms aren’t interchangeable spellings of one value the way an instant’s timestamp and ISO string are. Getting the kind wrong does far more damage — and does it more quietly — than picking the wrong notation for an instant.

For an instant, both answers are correct

Now narrow to instants. One fact dissolves half the argument on the spot: an integer Unix timestamp and an ISO 8601 string with an offset are two notations for the same instant, both anchored to UTC. They aren’t two competing “times” — they’re two spellings of the same information, one written as a number and one as text (exactly the notation layer of the four):

1700000000                    Unix timestamp (seconds)
2023-11-14T22:13:20Z          the same instant, written as ISO 8601

Both lines describe the same moment. So “integer or string” is not a correctness question — either one pins the instant unambiguously. It’s an engineering trade-off: readability, size, comparison and sort speed, cross-language portability. We’ll unpack that trade-off below. But first, keep out the option that’s wrong when you need an instant, because it’s the actual source of the bugs.

The dangerous third answer when you need an instant: a local time with no zone

A bare local wall-clock time isn’t wrong in itself — “opens at 9 AM every day” is legitimately a zoneless local time, and a LocalDateTime may be a wall time you’re keeping on purpose. It becomes the bug the moment you treat it as a value that needs to pin down a single instant. That’s a third option almost nobody chose on purpose, but which slips in through a default: a bare local wall-clock time with no offset at all.

2026-07-15 17:00:00        ← 17:00 in which zone? Nobody knows. Not enough information to identify an instant.

The string looks harmless, but it has thrown away the information needed to say which instant it is. The same text means a different moment in New York, in Shanghai, and in UTC. Once it lands in the database and gets read back, some default zone has to supply the missing half — and that default is usually the runtime’s own zone (which is exactly the seedbed for “off by a day” and “off by a few hours” bugs).

This trap is especially easy to fall into at the database-type level:

• MySQL’s DATETIME carries no zone semantics — it stores and returns a bare wall-clock value; while TIMESTAMP converts to and from UTC using the session time zone (not unconditionally), and its range is bounded, ending at 2038-01-19 03:14:07 UTC.
• PostgreSQL’s timestamp without time zone also stores a bare wall-clock value; only timestamptz treats it as a real instant (and even then it normalizes to UTC — it does not retain the input’s offset or zone name). That without time zone in the name is often misread as “defaults to UTC” — it actually means “no zone, semantics undecided.”
• The application layer — Python’s “naive” datetime, a Java LocalDateTime with no offset — is the same trap wearing a different face.

The rule is simple, and it runs through this whole cluster: when you store an instant, always store its anchor — either an explicit Z or offset in the notation, or a column type that is genuinely zone-aware in its semantics. A wall-clock time with no anchor is not an instant; it’s a puzzle waiting to be solved.

The engineering trade-off: integer vs offset-bearing ISO string

Once you’ve confirmed it’s an instant and attached its anchor, what’s left is the choice you can legitimately have a preference about. Each notation trades off differently:

Two sentences sum up the table. The integer is compact, comparable, fast, at the cost of being unreadable and needing discipline about its unit — hand seconds to something expecting milliseconds and you fling the date back to 1970. The offset-bearing ISO string is self-describing, portable, readable at a glance, at the cost of more space, a normalization step before comparison, and being easy to fat-finger into the zoneless wall-clock form — the third answer from the last section.

The lowest-effort third path: let the column type remember “this is an instant”

Much of that trade-off can be handed to the database itself. Many relational databases offer a native “instant with a zone” type — use it rather than inventing your own out of a bare integer or bare string. The exact behavior varies, though — some types normalize to UTC, some keep an offset, and some databases have no native time-zone-aware type at all — so check your database’s docs for the semantics:

• PostgreSQL’s timestamptz stores the instant as UTC — it does not keep the input’s offset or zone name. You write a value with an offset and it normalizes to the instant; you read it and it renders in the session’s TimeZone. You get the integer’s correct instant with a readable interface.
• On MySQL, either use DATETIME with an application-wide convention that “all values are UTC” (dodges 2038, but the discipline is on you), or use TIMESTAMP knowing it converts via the session time zone and tops out in 2038.
• At the API / JSON layer, use RFC 3339 — the stricter internet subset of ISO 8601 — because JSON has no date type at all, so an RFC 3339 string is the most direct, interoperable way to carry the offset with the value.

Notice something that’s often mistaken for a contradiction but isn’t: the column stores an “instant,” and you serialize it to JSON as an ISO string. That’s not a conflict — it’s that “store it as UTC” and “give me an ISO string” live on different layers. Storage cares about anchoring the instant to a reference; transport cares about a text both sides can parse. Same instant, different layers, each with its own notation.

A few details not to forget

With the big direction settled, a handful of details still trip people at the edges:

• Precision bites. An integer counting seconds can’t hold milliseconds or microseconds — if you need sub-second precision, either raise the unit (millis, micros) or use a string or column type that can express fractional seconds. Don’t silently truncate and expect it back.
• The sorting “superpower” has a condition. “ISO strings sort as text” only holds once they’re normalized first — same format, same precision, and ideally all converted to UTC Z. Mix offsets and text order stops equaling time order.
• Store an expiry as a moment, not a duration. For “expires 30 days after creation,” store that expiry instant, not the number “30” with a note — comparison and queries stay trivial. As for which calendar day that expiry lands on, don’t compute it by “add 30 × 86400 seconds” (that drifts across DST and month lengths) — hand the plain “how many days between” and “what date is 30 days out” work to a date calculator.
• A future local event stores a zone name, not an offset. Mentioned above, but worth nailing again: +02:00 is a result the rules compute for one instant, and it changes; Europe/Berlin is the zone-with-rules itself, and that’s what a future commitment should store.

When debugging, if you’re holding a bare integer and can’t tell whether it’s seconds or milliseconds or which day it lands on, drop it into a Unix timestamp converter and read it both ways; if you want to see how a stored instant reads across your users’ zones, a time zone converter lines them up side by side.

A decision table

Collapsing all of the above into a “store this” table covers nearly every case:

The closing checklist

Next time “timestamp or ISO string” flares up, don’t start the fight at integer versus string. Ask these in order:

1. What kind of value is it? Instant, calendar date, future local commitment, or duration? The “two notations, one value” equivalence is only about the instant; for the other three, getting the kind wrong is worse than getting the notation wrong.
2. Does it carry its anchor? An instant must carry a Z/offset, or live in a column type that is genuinely zone-aware. A bare wall-clock value with no offset is not an instant; it’s a puzzle.
3. Integer or string — choose on the trade-off. Want compact, comparable, fast? Integer (but pin the unit). Want self-describing, portable, readable? Offset-bearing ISO (but normalize before comparing). If a native zone-aware column type is available, use it and get both.
4. Are the details lined up? Enough precision (no truncated sub-seconds), normalization before sorting, an expiry stored as a moment not a duration, a future event stored as a zone name not an offset.

Underneath all four steps is the line that runs through the whole cluster: first tell whether it’s an instant or something else, give the instant its anchor, and only then argue about notation. Get the order right and “timestamp or ISO string” turns from an unwinnable spat into an engineering decision with a clear trade-off you can explain at any time.