ISO 8601 vs Unix Timestamps: Which Format to Use and Why the Wrong Choice Causes DST Bugs

ISO 8601 UTC and Unix timestamps represent the same moments but encode different assumptions — and the most pervasive timestamp bug is storing DATETIME without timezone information, which breaks when servers change timezones, when DST transitions create missing hours, or when users in different timezones submit local timestamps. Here's the practical guide: when to use each format, why ISO 8601 UTC sorts lexicographically, and the date-only vs datetime semantic distinction that causes birthday-display bugs.

ISO 8601 (`2024-11-15T14:30:00Z`) and Unix timestamps (`1731677400`) represent the same moment — but they encode different assumptions about what "same moment" means, and choosing the wrong format for your storage or exchange context causes bugs that only surface months after deployment when someone queries data across a DST boundary

The previous articles on this site covered Unix timestamps and timezone bugs, ISO 8601 date handling mistakes, Y2K and Y2K38 bugs, and why 1970 was chosen as the Unix epoch. This article addresses date format selection for APIs and databases — the practical guidance on which format to use when, why mixing formats causes bugs, and how to design date field conventions for a system that might eventually be used internationally.

The three practical timestamp formats and what they encode

Unix timestamp (integer):

Example: 1731677400
Represents: seconds since 1970-01-01 00:00:00 UTC
Properties: timezone-unambiguous (always UTC), compact, easy to compare and sort, requires conversion to display
Where used: system internals, logging, event ordering, databases where arithmetic on timestamps is common

ISO 8601 with timezone offset:

Example: 2024-11-15T14:30:00+05:30
Represents: a specific local date and time with the UTC offset at that moment
Properties: human-readable, explicitly encodes the local time and the UTC offset, but the offset may not be the current offset (it's the offset at that specific moment, which matters for DST analysis)
Where used: API request/response bodies, human-readable configuration, calendar systems, email headers

ISO 8601 in UTC (Z suffix):

Example: 2024-11-15T09:00:00Z
Represents: a specific UTC moment
Properties: timezone-unambiguous, human-readable, sorts lexicographically (alphabetical sort = chronological sort)
Where used: APIs, database storage, log files — the recommended default for most cases

The lexicographic sort property: why ISO 8601 UTC sorts correctly

One of ISO 8601's most practically useful properties: when dates are formatted in the full YYYY-MM-DDTHH:MM:SSZ format, alphabetical (lexicographic) sort produces chronological sort. No date parsing required to sort date strings in this format.

This holds because:

The most significant unit (year) comes first
Each subsequent unit is left-zero-padded to the same length
The format is consistent

Where this fails:

2024-11-15T09:00:00+05:30 (local timezone) sorts wrong against 2024-11-15T06:00:00Z — these are the same moment, but lexicographically the first is "later" even though they represent the same instant
11/15/2024 (US format) sorts completely wrong
15 Nov 2024 sorts wrong

Database design implication: storing ISO 8601 UTC strings in a VARCHAR column still sorts chronologically without date parsing — useful when the database's date type has limitations or when storing in formats that need both human readability and sortability.

The "local time in the database" bug

The most pervasive timestamp bug in web applications:

A developer builds an application in a single timezone (say, CET, UTC+1 in winter / UTC+2 in summer). They store "event time" in the database as a DATETIME without timezone — 2024-11-15 14:30:00 — which the database stores as a naive local time.

The bug surfaces when:

The server is migrated to a different timezone (common when moving to a cloud provider with UTC-default servers)
The application is used by users in different timezones who submit timestamps in their local time
A database backup is restored on a different server with a different local timezone setting
The application goes through a DST transition — events scheduled for "tomorrow at 2:30 AM" don't exist on the day clocks spring forward

The fix: store all timestamps as UTC — either as Unix timestamps, as ISO 8601 UTC strings, or as a database TIMESTAMP type (which MySQL, PostgreSQL, and SQL Server all interpret as UTC internally when properly configured).

Converting on read, not on write: store UTC, display local. Convert the UTC timestamp to the user's local timezone at display time (using their browser's timezone or their account's timezone setting), not when writing to the database.

Date-only vs datetime: different types for different semantics

A "date" (calendar date without time) and a "datetime" (specific moment in time) have different semantics and different appropriate types:

Date: "User's birthdate: 1990-05-15" — there is no "time" component; this is a calendar date that means the same thing regardless of timezone. Stored as DATE type in databases.

Datetime: "Order placed at: 2024-11-15 14:30:00 UTC" — a specific moment. The timezone must be known to interpret this correctly. Stored as TIMESTAMP (UTC-aware) in databases.

The confusion: developers sometimes store datetimes as dates (losing precision and timezone information) or dates as datetimes (adding false precision and creating DST-edge-case bugs for dates near midnight in different timezones).

Birthdate stored as datetime example bug: user born on December 31, 1990, stored as 1990-12-31 00:00:00 UTC. When displayed in UTC-5, this becomes 1990-12-30 19:00:00 — which might be displayed as December 30 depending on how the application formats it. A birthday stored as a date (1990-12-31) has no timezone interpretation problem.

How to use the Timestamp Converter on sadiqbd.com

For API debugging: convert a Unix timestamp from an API response to a human-readable datetime to verify it represents the expected moment — remembering that the displayed time is UTC unless you specify a timezone offset
For database timestamp investigation: convert suspicious datetime values from your database to check whether they represent the intended UTC moment or have been incorrectly stored in local time
For conversion between formats: if receiving Unix timestamps from one system and needing to produce ISO 8601 for another (or vice versa), the converter provides both formats for any moment

Frequently Asked Questions

Should I use a database DATETIME or TIMESTAMP type for storing event times? TIMESTAMP for event times that represent specific moments; DATE for calendar dates; DATETIME sparingly. In MySQL, TIMESTAMP is stored as UTC and converted to/from the connection timezone on read/write — making it timezone-aware by default. DATETIME is stored as a literal value without timezone interpretation — making it appropriate only for times that are inherently timezone-relative (a local appointment that should show the same "clock time" regardless of where the user is, like a recurring alarm that should fire at "9 AM your time"). In PostgreSQL, TIMESTAMP WITH TIME ZONE (timestamptz) stores UTC internally; TIMESTAMP WITHOUT TIME ZONE stores a naive local timestamp. The guidance: default to TIMESTAMP (or timestamptz in PostgreSQL) for nearly all cases; use DATE for pure calendar dates; use DATETIME/TIMESTAMP WITHOUT TIME ZONE only when you specifically need timezone-naive local-time semantics.