Microsecond Epoch: Datetime vs Integer Storage

The Silent Precision Murder: Why Your Microsecond Epoch Storage Is Lying to You

Most developers storing microsecond Unix timestamps in a database do something like this: they take a value like 1697254200123456, store it in a standard DATETIME column, and move on. Then, weeks later, when they run a timestamp converter tool to debug a race condition or reconstruct an event sequence, the microsecond portion has vanished. The database rounded it. Or truncated it. Or silently shifted it during a timezone conversion. The developer never knew.

This is the single most common failure pattern I see when reviewing timestamp storage implementations across Unix timestamp tooling workflows. And it's almost always preventable — if you understand what your database's datetime functions actually do under the hood.

Let's walk through the typical mistakes, then fix each one by comparing two storage approaches side by side: storing raw epoch integers versus using native high-precision datetime types.

Mistake #1: Assuming DATETIME Means Microsecond-Accurate

What People Do

The instinct is reasonable. You have a microsecond-precision Unix timestamp — say 1697254200123456 microseconds since epoch — and you want it in a human-readable datetime column. So you convert it using a database function like FROM_UNIXTIME() in MySQL or to_timestamp() in PostgreSQL, insert the result into a DATETIME column, and trust that the precision survived.

It didn't.

What Actually Happens

MySQL's standard DATETIME type, without fractional seconds declared, truncates everything after the second. Even DATETIME(6), which supports microsecond precision, behaves differently depending on the version and the conversion function used. PostgreSQL's timestamp without time zone does store microsecond precision natively — but if you passed your epoch through an intermediate integer division or a float cast during conversion, you may have already lost data before it hit the column.

The evidence is in the numbers. Take the timestamp 1697254200123456. If you naively divide by 1000000 to get seconds and pass that to FROM_UNIXTIME() in MySQL, you get 2023-10-14 00:10:00. The 123456 microseconds? Gone. Discarded during the integer division. Your database didn't even get a chance to store them.

The Fix

If you're going to use native datetime types, declare the fractional precision explicitly. In MySQL, use DATETIME(6). In PostgreSQL, timestamp already handles six digits of fractional seconds, but make sure your input path preserves them. Convert using multiplication, not division, when moving between epoch and datetime representations:

1697254200.123456 seconds × 1000000 = 1697254200123456 microseconds

No precision lost in either direction.

Mistake #2: Storing Microsecond Epochs as BIGINT Without a Validation Strategy

Approach A: Raw Integer Storage

The alternative approach — and the one many Unix timestamp tool developers actually prefer — is to skip native datetime types entirely. Store the microsecond epoch as a BIGINT. Value goes in, value comes out, no conversion drama.

This sounds bulletproof. And in many ways, it is. A BIGINT can hold values up to 9223372036854775807, which covers microsecond timestamps well beyond the year 2262. No timezone ambiguity. No implicit rounding. No dialect-specific datetime function behavior to memorize.

But there's a catch. Without validation, BIGINT storage invites a different class of error: unit confusion.

The Unit Confusion Problem

I've reviewed databases where the same BIGINT column contained a mix of millisecond timestamps, microsecond timestamps, and even a few nanosecond values from a misconfigured IoT sensor pipeline. The column looked clean. The queries returned numbers. But when a developer plugged those values into a Unix timestamp converter, the dates were off by factors of a thousand.

A microsecond timestamp for October 2023 is 1697254200123456. A millisecond timestamp for the same instant is 1697254200123. These look similar enough that a casual reviewer won't catch the discrepancy — but they're off by three orders of magnitude.

Approach B: Native Datetime(6) Storage

The native datetime approach, by contrast, enforces a format. You can't accidentally store a millisecond value in a DATETIME(6) column — the database will interpret whatever you give it as a datetime, and the conversion function forces you to be explicit about units.

Here's the side-by-side comparison:

The Fix

If you choose BIGINT — and for high-throughput Unix timestamp tooling, I generally recommend it — add a check constraint or application-level validation that enforces the expected digit count. A microsecond timestamp in the 2020s should be a 16-digit integer. A millisecond timestamp should be 13 digits. This is a simple, cheap guardrail:

1697254200123456 → 16 digits → valid microsecond epoch

1697254200123 → 13 digits → millisecond epoch, reject or flag

If you choose native datetime types, declare DATETIME(6) explicitly and never use intermediate float conversions. Floats introduce rounding errors at the microsecond level that compound over large datasets.

Mistake #3: Letting Timezone Functions Touch Your Epoch Values

The Hidden Corruption

Here's a subtle one. A developer stores microsecond timestamps correctly as BIGINT. Then, for reporting, they run a query like this:

SELECT FROM_UNIXTIME(my_epoch / 1000000) AT TIME ZONE 'America/New_York'

The division produces a float. FROM_UNIXTIME interprets it as seconds. The timezone function shifts the result. The output looks correct in the report. But the developer has now created a derived value that can't be round-tripped back to the original epoch without lossy assumptions about the timezone offset and the float precision of the division.

This is especially dangerous in Unix timestamp converter workflows where users paste a datetime into a tool and expect to get the exact original microsecond epoch back. They won't. The float division already discarded sub-microsecond information, and the timezone conversion added offset ambiguity.

The Fix

Keep timezone operations in the presentation layer, not the storage or conversion layer. Store the raw epoch. Convert to datetime only when displaying to users, and always preserve the full microsecond value through integer arithmetic:

seconds = epoch_micros DIV 1000000

micros = epoch_micros MOD 1000000

Reassemble on output. No floats. No loss.

Mistake #4: Ignoring Database-Specific Datetime Function Behavior

MySQL vs PostgreSQL vs SQLite

Each database engine handles datetime functions differently, and assuming parity across them is a mistake that bites quietly.

MySQL FROM_UNIXTIME() accepts a second argument for format strings but historically had issues with fractional seconds before version 5.6.4. If you're on an older MySQL instance, DATETIME(6) isn't even available. Your microsecond data gets truncated at the second boundary, silently.

PostgreSQL handles this more gracefully. The to_timestamp() function accepts double-precision floats, which means you can pass 1697254200.123456 and get microsecond precision in the result. But double-precision floats only carry about 15 significant digits. A microsecond timestamp like 1697254200.123456 has 16 significant digits. The last digit may round unpredictably.

SQLite doesn't have a native datetime type at all. It stores everything as text, real, or integer. If you're using SQLite for a lightweight timestamp tool, store epochs as integers and format them on output. Don't rely on SQLite's datetime functions for precision-critical work.

The Fix

Know your engine. Test the round-trip: insert a known microsecond epoch, query it back, and compare. If the values don't match exactly, your storage path has a precision leak. This takes five minutes and catches problems that would otherwise lurk for months.

Recommendation: Which Approach Wins

After reviewing both approaches across multiple Unix timestamp tooling projects, the evidence points clearly in one direction — but with a condition.

For storage, raw BIGINT microsecond epochs win. They're lossless, engine-agnostic, timezone-immune, and simple to validate. The downside — poor human readability in raw queries — is solved by keeping a timestamp converter function or view in your application layer, not by sacrificing precision in the storage layer.

For querying and reporting, native DATETIME(6) or PostgreSQL timestamp types win — but only as derived, indexed views built from the underlying epoch integers, not as the primary storage.

The pattern that consistently produces the fewest bugs: store BIGINT, validate digit count, convert to datetime at query time using integer arithmetic, and never let a float touch your microsecond values. That's the approach I'd recommend to anyone building tools in this niche, and it's the one that survives the round-trip test every time.