Every team eventually hits the same question: should the created_at column in Postgres be BIGINT, TIMESTAMPTZ, or a stringified ISO-8601? Should the frontend serialize with Date.now() or Math.floor(Date.now()/1000)? Should Kafka headers carry seconds or milliseconds? The "Unix timestamp" feels like a single concept, but in practice it fragments the moment it crosses a service boundary, and the cost of that fragmentation shows up as 2 AM incidents and rollbacks.
This article is the playbook I wish I had when I shipped a payments rewrite where three teams disagreed on units for six weeks. It walks through how to decide where seconds-since-epoch belongs in a modern stack, what to migrate first, how to keep ownership clear, and how to verify the result without writing a custom microservice. There is a deeper walkthrough of the SQL specifics in the Lizely guide on converting Unix timestamps in SQL queries, which I'll point to at the natural moment below.
Why the "Just Store It as a Number" Advice Ages Poorly
A Unix timestamp is an integer count of seconds since 1970-01-01T00:00:00Z, ignoring leap seconds. That definition is stable, but the assumptions stacked on top of it are not:
- JavaScript's
Date.now()returns milliseconds, not seconds. Most web frontend bugs come from this single mismatch. - Some legacy Windows APIs use 100-nanosecond ticks since 1601.
- Some telemetry pipelines emit decimal seconds with sub-second precision.
- Some databases store epoch internally and surface it as
TIMESTAMPTZfor you.
The official Unix time definition on Wikipedia makes the "ignoring leap seconds" caveat explicit, which is the reason a naive DATEADD(s, …) can drift a second or two every few years against civil time. That drift is not your bug to solve, but you do need to decide whose clock you trust.
The Decision: Where Does the Integer Live?
I run new services through this checklist before writing the first migration:
- What does the protocol on the wire actually carry? gRPC, Protobuf, and most JSON APIs let you pick. Pick the unit the receiving client expects, not the sender's convenience.
- Who is the canonical source of time? If it's your database, store a typed timestamp there. If it's a third-party system you cannot change, mirror its unit exactly and document it in the schema.
-
How will humans read this in five years? A
TIMESTAMPTZcolumn reads itself. An integer column forces every future engineer to ask "seconds or milliseconds?" - What is the indexing story? Range queries on integers are slightly cheaper, but typed timestamps in modern Postgres are essentially free in practice.
- What is the export story? If a value will ever land in a CSV for an analyst, an ISO-8601 string is friendlier than either.
The default I reach for in 2024: TIMESTAMPTZ in the database, ISO-8601 strings over HTTP, integers only at the very edges where a foreign system forces it. When I do keep integers, I name the column created_at_epoch_seconds or created_at_epoch_ms — the unit is part of the contract.
The Wire: Three Patterns and Their Failure Modes
Pattern A: Plain JSON integers
Most teams ship {"ts": 1716123456} and never look back. The failure mode is silent: a client SDK written in JavaScript reads it and assumes milliseconds. You discover it when a chart shows "1970" or "2055."
Pattern B: Typed JSON via ISO-8601
This is what the WHATWG HTML living standard recommends for browser-facing APIs, since Date parses ISO-8601 natively. The WHATWG HTML specification on the Date state describes the format and the simplification rules. Round-tripping through ISO-8601 loses sub-second precision and forces you to pick a timezone offset, but it eliminates the seconds-vs-milliseconds class of bug entirely.
Pattern C: Strings with explicit units
Some teams ship {"ts_seconds": 1716123456} or "1716123456000ms". This is the loudest pattern: the unit is in the field name. I recommend it whenever the value crosses a system boundary that has historically been confused — payment timestamps, audit trails, anything that will eventually be subpoenaed.
The Database: A Concrete Migration Recipe
When you inherit a BIGINT column and want to move to TIMESTAMPTZ, the order of operations matters more than the SQL. This is the order I ship:
-
Add the typed column in parallel.
ALTER TABLE events ADD COLUMN created_at_tz TIMESTAMPTZ; -
Backfill in a single transaction.
UPDATE events SET created_at_tz = to_timestamp(created_at_epoch_seconds); - Ship dual writes from the application for at least one release cycle. Read still hits the integer column; writes populate both.
- Run a shadow read that compares the two columns for a sample of rows. If you cannot reconcile 100% of rows, stop and investigate before promoting.
- Flip reads to the typed column behind a feature flag. Watch p99 latency and error rate.
- Drop the integer column only after the application no longer references it and downstream consumers have migrated. Leave a view for one more cycle.
The Postgres to_timestamp function in the official docs is the workhorse for step 2. Note that to_timestamp interprets a double precision argument as seconds-since-epoch — pass a BIGINT and it interprets it as seconds since 2000-01-01, which is a footgun I have watched three teams hit. The Lizely guide linked earlier has the exact dialect-by-dialect comparison for MySQL, BigQuery, and Snowflake if you are polyglot.
The Edge: Where an Online Converter Earns Its Place
Even with great defaults, there are moments when an engineer needs to convert a single weird value fast: a customer support ticket that says "the system says 1345678901 but the invoice says 2012," a CSV export from a vendor that mixed units, or a Slack thread where a teammate pastes a 13-digit number and asks "what time is this?"
This is the right moment for a lightweight browser tool. I keep a Unix timestamp converter bookmarked for exactly these triage moments: paste the integer, eyeball the result in my local timezone, paste it back into the chat with the unit annotated. It is the modern version of a calculator at a whiteboard — not a place to live, but a place to verify. The choice of unit (seconds vs milliseconds vs microseconds) is exposed in the UI, which is what prevents the calculator from recreating the bug you are trying to debug.
Ownership: Who Is on the Hook at 2 AM?
The hardest part of this work is not technical; it is organizational. Three rules that have saved my teams:
- The schema owner owns the unit. If you change the column type, you own the migration, the backfill, the dual-write window, and the rollback plan.
- The wire format is owned by the API working group, not by individual teams. Ad hoc choices drift.
- The clock source is documented in the README. "All timestamps in this service are UTC milliseconds since Unix epoch" is a sentence that prevents a whole class of incident.
A "who owns this" line in the runbook costs nothing and pays off the first time someone on a different team opens a page and sees a wrong number.
Frequently Asked Questions
Should I ever store seconds vs milliseconds inside the application itself?
Store seconds if you are integrating with a system that uses seconds (classic Unix tooling, many message brokers' default codecs, some payment APIs). Store milliseconds if your primary consumer is browser JavaScript or a JVM service using java.time.Instant.ofEpochMilli. Never store microseconds in a column named timestamp — name the unit or use a typed timestamp.
How do I detect a mixed-unit bug before it ships?
Add a runtime assertion in your serializer: if the value is greater than roughly 10^11, treat it as milliseconds and convert to seconds before transmitting. The threshold is ~3.3 × 10^9 for seconds (year 2070) and ~3.3 × 10^12 for milliseconds (year 2100 in ms). Logging a warning when a value crosses the threshold surfaces the bug without blocking deploys.
What about ISO-8601 strings with offsets vs Z?
Prefer Z (UTC) on the wire to avoid the "is +02:00 wall time or UTC?" question. The RFC 3339 date-time format is the strict subset of ISO-8601 you actually want, and it defines Z as the explicit UTC marker. Most parsers handle it correctly; many mishandle local times without offsets.
Do I need to worry about leap seconds in practice?
No. Civil time does, Unix time does not. If your service depends on civil seconds (billing windows, regulatory deadlines), use a library that tracks leap seconds — TAI, google-civil-time, or your language's IANA database plus a leap-second table. If your service depends on "elapsed time between two events," Unix seconds is fine and you should not convert through a calendar.
This article was drafted with AI assistance and reviewed for technical accuracy before publishing.
