Epoch Seconds vs Milliseconds vs Microseconds: A Storage Strategy Guide for System Admins

1735689600 vs 1735689600000: Same Moment, Two Very Different Post-Mortems

Those two numbers are the same instant — January 1, 2025, 00:00:00 UTC — written once in epoch seconds and once in milliseconds. Feed the wrong one to a system expecting the other, and your monitoring dashboard will confidently report that an outage began on January 21, 1970. Run the mismatch the other direction, and the same incident gets scheduled for somewhere around the year 57,000, which is an ambitious SLA even for us.

I'm the sysadmin who gets pinged when that happens. Fifteen years of running log pipelines, a Postgres estate, a couple of Java services nobody admits to owning, and one Node edge that keeps everyone humble has taught me a single durable lesson: the unit you store a Unix timestamp in is a storage strategy decision, not a formatting detail. This is the guide I wish somebody had slid across my desk before I learned it the expensive way. It's written for the person whose actual job is deciding what gets written where — and keeping it readable five years later.

What Each Unit Buys You — and What It Bills You

Think of seconds, milliseconds, and microseconds as three tools on the same belt. They measure the same wall clock. They differ in resolution, in the hardware that loves them, and in what they cost you at rest.

Epoch seconds: the 32-bit veteran

Ten digits today. One-second resolution. Fits in a signed 32-bit integer all the way up to 2147483647 — January 19, 2038, 03:14:07 UTC, the famous last call. Every converter, every cron expression, every textbook speaks it fluently. For business facts — an order created, an invoice paid, a user registered — a second is honest precision. No invoice ever needed sub-second truth.

Milliseconds: the JavaScript handshake

Thirteen digits today. Date.now() and Java's System.currentTimeMillis() both speak it natively, which is exactly why it leaks into everything they touch. It requires 64 bits: a 32-bit integer overflows at 2,147,483,647 milliseconds, roughly 25 days after the epoch. Milliseconds are also where event ordering starts to actually work — two things happening inside the same second stop being indistinguishable.

Microseconds: the specialist

Sixteen digits. The gettimeofday() heritage, beloved by distributed tracing, profilers, and anyone for whom 40 microseconds is a meaningful distance between two events. A signed 64-bit integer holds microseconds comfortably for about 292,000 years, so overflow will not be your problem. Paying eight bytes per value without a very good reason will be.

The Storage Math That Wins Budget Meetings

Raw, uncompressed cost per stored timestamp:

Now scale it. A modest log estate ingesting one billion events per month pays 4 GB for INT32 seconds, 8 GB for milliseconds, and 24 GB for ISO 8601 strings. That string column costs 20 GB more per month than the seconds integer — 240 GB a year — for a single field, before you've indexed anything.

The honest counterpoint: compression exists, and it's good. Columnar stores using delta-of-delta encoding can squeeze regularly-spaced timestamps down to roughly one bit per value, at which point the unit choice barely moves the disk needle in your log store. Where it moves needles is row-oriented OLTP tables, wide indexes, and — always — human attention. The cheapest timestamp storage strategy is the one nobody has to think about twice.

Where Each Unit Belongs: A Field Guide by Workload

Business records: seconds, or a native type

Contrast an order row with a clickstream event. The order's created_at is a fact at second granularity — it happened, full stop. If your database offers a native timestamp type, take it: Postgres timestamptz is 8 bytes, carries microsecond resolution internally, and comes with arithmetic included. The clickstream event 40 milliseconds later, though, is a different animal.

Logs and metrics: milliseconds

Here's the concrete case. A login event and its audit-trail sibling land 40 ms apart. Stored in seconds, they share a value and their order is a coin flip. Stored in milliseconds, 1735689600040 sorts after 1735689600000 and the story tells itself. Cross-host clock skew is routinely tens of milliseconds, so milliseconds are also the resolution at which ordering between nodes becomes a negotiation worth having rather than a fantasy.

Traces and profiles: microseconds

Span durations, cache hits versus misses inside a single request, lock contention — this is microsecond country. Nanoseconds occasionally creep in via clock_gettime() and Python's time.time_ns() — nineteen digits — and that's fine in memory during a profiling run. Persisting nanosecond timestamps to disk is almost always precision you can't defend.

Conversion Traps I Have Personally Stepped In

Every trap below is a true story from my own estate, paired with the fix that finally stuck:

• Python's time.time() returns seconds as a float. A scriptwriter assumed milliseconds and produced a week of 1970-dated records. The fix: time.time_ns() // 1_000_000 when you want integer milliseconds, and a comment saying so.
• Java writes milliseconds; Postgres reads seconds. extract(epoch from ...) returns seconds, so glue code that copies a Java long straight into an epoch-seconds comparison is off by exactly 1000×. That's the year-57,000 dashboard. Divide at the boundary, explicitly, once.
• MySQL's UNIX_TIMESTAMP() returns whole seconds by default. Fractional precision vanishes silently — no error, no warning, just rounding you find out about during an investigation.
• JavaScript's Math.floor(ms / 1000) versus keeping the float. One renders 00:00:00, the other 23:59:59.999 in the same pipeline. Pick the floor, apply it in exactly one place, and document it.
• Storing milliseconds in a column documented as seconds. This isn't a bug; it's a bug report with a launch date. The schema comment is part of the storage strategy.

Hard-Won Habits: Rules That Keep the Units Straight

After enough of the above, I wrote a one-page standard and taped it inside the runbook. It has five rules:

1. Name the unit in the field. event_ms, created_at_sec, span_start_us. A column called timestamp is a name for an argument, not a schema.
2. Store raw epoch, convert at the display edge. Never persist pre-formatted local-time strings. On the autumn clock change, 1:30 AM happens twice in some timezones and not at all in others — a fact your incident timeline should not have to represent.
3. UTC everywhere internally. Timezone conversion is a presentation concern, like choosing a font.
4. Keep a canonical test value. 1735689600 must render as 2025-01-01 00:00:00 UTC. Wire that assertion into pipeline canaries and unit tests. One line of test, years of peace.
5. Write the standard down. Undocumented conventions are just rumors with good uptime.

Inherited the Mess? A Triage Checklist That Works

Most of us don't get a greenfield. We get a database where the orders table speaks seconds, the event store speaks milliseconds, and one C++ collector mutters in microseconds. Before you migrate anything, run the digit-count sniff test on sampled values:

• 10 digits → epoch seconds (the ten-digit era began 2001-09-09, when the counter passed 999999999)
• 13 digits → milliseconds
• 16 digits → microseconds
• 19 digits → nanoseconds, and my condolences

One caveat: pre-2001 timestamps are only nine digits — 946684800 is January 1, 2000 — so don't panic when the sniff test comes back one short on archival data.

Then verify before you trust. Run min() and max() over the column: a "seconds" column whose max sits near 1.7 trillion is telling you something. Find the writer — if it was JavaScript or Java, assume milliseconds until proven otherwise. Convert a handful of sample values in a Unix timestamp converter and confirm they land on plausible dates. And when you do migrate, do it with a typed ALTER and a canary batch, not a big-bang rewrite. I've watched a big-bang timestamp migration take a billing system offline for a day; I've never seen a canary do worse than ruin someone's lunch.

Decide Before 2038 Decides for You

The 32-bit seconds counter stops on January 19, 2038, at 03:14:07 UTC. That deadline is doing you a favor: it forces the conversation your estate should have had anyway. The unit you choose is a contract with your future self and everyone who inherits your tables.

My defaults, earned the hard way: seconds for business facts, milliseconds for events, microseconds for traces, native timestamp types where the database offers them, and the unit in the field name — always. When a mystery number shows up with no documentation and a suspicious number of digits, count the digits, convert a sample, then commit. The converter tab is a tool. It shouldn't be a lifestyle.