Timestamps in APIs & Databases: Storage, Formats & Timezone Pitfalls

Why Timestamp Handling Breaks More Systems Than It Should

A timestamp looks like one of the simplest values a system can hold — a moment in time, encoded as a number or a string. In practice, it's one of the most common sources of subtle, hard-to-reproduce bugs in any codebase that touches a database and an API at the same time. The reason isn't the underlying math; it's that "timestamp" quietly means several different things depending on where you look — a column type, a wire format, an application-layer object, and a display string — and each of those layers can disagree about timezone, precision, and format without throwing an obvious error.

Most of these bugs are avoidable with a small set of decisions made deliberately at the start of a project rather than discovered accidentally months in. This guide walks through those decisions: what to store, how to format it for an API, and how to keep the two in sync as a system grows.

Choosing a Storage Format: INTEGER vs TIMESTAMP vs TIMESTAMPTZ

At the column level, there are really three broad approaches, and each has a real, current tradeoff rather than one being universally correct.

For the large majority of application data — when something happened, was created, or was last modified — a timezone-aware type is the safer default. The narrow exception is data that's genuinely about a repeating local time (a daily alarm, a weekly recurring meeting slot) where you want the wall-clock time itself to survive a timezone change unaffected.

Why "Always Store UTC" Is Necessary But Not Sufficient

"Just store everything in UTC" is good advice, but it's incomplete for a specific and common case: recurring events tied to a person's local time. If a user schedules a weekly reminder for 9 AM in their timezone and you only store the UTC-converted instant, that reminder silently shifts by an hour whenever daylight saving time changes on either end — because you stored a fixed point in time, not "9 AM, local, recurring." For genuinely recurring local-time events, store both the UTC instant for the next occurrence and the IANA timezone name (like America/Chicago, not a fixed UTC offset like -05:00) so the next occurrence can be recalculated correctly across DST transitions.

ISO 8601 in JSON APIs: The De Facto Standard

For timestamps traveling over an API in JSON, ISO 8601 (specifically its RFC 3339 profile) has become the practical default — a string like 2026-08-30T14:22:05Z. It's unambiguous, sorts correctly as plain text, and every mainstream language's standard library can parse it without a third-party dependency. The trailing Z denotes UTC explicitly; if you instead include a fixed offset like +05:30, make sure your parsing code distinguishes between "this moment, expressed with an offset" and "this local wall-clock time," since conflating the two is a frequent source of off-by-a-few-hours bugs when data crosses systems.

Seconds, Milliseconds & the Precision Mismatch Between Systems

Beyond string formats, plenty of APIs still exchange timestamps as raw numbers, and this is where seconds-vs-milliseconds confusion causes real damage — a millisecond value misread as seconds lands the resulting date somewhere in the year 1970, and the reverse produces a date far in the future. The underlying precision fundamentals (why JavaScript defaults to milliseconds while most Unix-heritage tooling defaults to seconds) are covered in depth in What Is Unix Time? and the practical per-language conversion code lives in Timestamps in JS, Python & PHP — worth bookmarking both if your API needs to interoperate with several of these ecosystems at once. Whichever precision you choose for a given endpoint, document it explicitly in the field name or the API reference rather than leaving it implied.

Common Database Column Types Across Popular Systems

The same conceptual choice (timezone-aware vs not) is implemented differently enough across popular databases that assuming one system's behavior carries over to another is a frequent source of migration bugs.

The practical implication: if you migrate data between these systems, don't assume a column labeled "timestamp" behaves identically on both sides. Confirm the actual conversion behavior on both ends before trusting a straight copy.

Designing API Timestamp Fields: Naming & Convention

A few small naming conventions prevent a disproportionate number of integration questions later. Suffix field names with their unit when you're not using ISO 8601 strings — created_at_ms rather than an ambiguous created_at that could be seconds, milliseconds, or a string depending on the reader's assumption. Keep create and update timestamps immutable and server-set rather than accepting them from API clients, unless the endpoint is explicitly designed for backfilling historical data — allowing clients to set their own created_at invites both accidental and intentional inconsistency in your data's timeline.

It's also worth distinguishing, in your own documentation, between a fixed UTC offset and a named timezone — they answer different questions and get conflated constantly. An offset like +05:30 tells you the relationship between a specific instant and UTC at that moment, nothing more. A named timezone like Asia/Kolkata encodes a set of rules (including historical and future DST transitions, where applicable) for converting any instant to local time. If your API needs to answer "what will this be in local time next year," an offset alone can't do that reliably wherever the region observes DST — you need the named zone.

Handling Client-Submitted Timestamps Safely

Whenever a timestamp arrives from outside your system — a webhook payload, a form submission, a third-party API response — validate it strictly rather than parsing leniently. A permissive parser that accepts multiple formats "to be helpful" also accepts malformed or ambiguous input that later causes a silent misinterpretation rather than a clear error at the boundary. Reject anything that doesn't match your documented format exactly, and if a client-supplied timestamp lacks explicit timezone information, don't guess — either require it or clearly document the assumed default.

Pagination & Timestamps: Cursor-Based APIs

Timestamps show up in API design beyond just data fields — they're also commonly used as pagination cursors. A timestamp-based cursor (fetch everything after 2026-08-30T14:22:05Z) stays stable even as new rows are inserted between page requests, unlike offset-based pagination, which can skip or duplicate records when the underlying dataset changes mid-scroll. The tradeoff is that timestamp cursors need a tie-breaker (commonly a secondary sort on a unique ID) for the rare case where multiple records share the exact same timestamp, since millisecond precision isn't always fine-grained enough to guarantee uniqueness at scale.

Legacy 32-Bit Columns: Still a Live Risk in 2026

Teams migrating older systems occasionally discover a timestamp column that was defined as a 32-bit signed integer years ago, which will overflow in January 2038 regardless of which database platform it lives in. Most current database-native timestamp types default to 64-bit storage, so this risk is specifically about manually chosen INT columns inherited from an older schema, not something new schemas typically introduce by accident. If you're auditing a legacy system for this, the full mechanics and a systematic way to check your own systems are covered in The Year 2038 Problem Explained.

Real-World Scenarios

A pattern worth noticing across all four: in each case, the naive fix (just store UTC, just store the offset) works fine until a specific edge case exposes the gap — a DST transition, a replay attempt, a cross-timezone report. Building the extra field or the extra validation step in from the start is cheaper than the migration that follows discovering the gap in production.

Migration Checklist: Moving Off Legacy Integer Timestamps

1. Audit every timestamp column for its actual type and whether it's genuinely 32-bit or 64-bit before assuming either.
2. Decide on one canonical wire format (typically ISO 8601 UTC) for all API responses going forward, even if internal storage varies.
3. For recurring local-time data, add an explicit IANA timezone column rather than relying on a fixed offset.
4. Update API documentation to state precision (seconds vs milliseconds) explicitly per field, not just once at the top of the document.
5. Test the migration against DST transition dates specifically, not just arbitrary sample dates, since that's where silent bugs concentrate.

Summary

Timestamp bugs are rarely about the math — they're about a mismatch in unstated assumptions between a database column, an API contract, and the code reading both. Choosing a timezone-aware storage type by default, standardizing on ISO 8601 for API payloads, documenting precision explicitly, and storing IANA timezone names alongside genuinely recurring local-time data covers the overwhelming majority of real-world cases without needing anything exotic.

Frequently Asked Questions