Epoch integers vs timestamp columns: why the unit belongs in the schema

created_at: 1700000000 or 1700000000000? — the column that two services wrote in different units for six months

A column called `created_at` containing both 1,738,578,000 and 1,738,578,000,000 cannot be interpreted consistently. Sorting numerically separates writers by scale rather than chronology, and automatic detection in every reader hides corruption instead of repairing it. The schema failed to preserve a required unit.

Before migration, profile values by producer and compare representative rows with independent event evidence. Do not divide all long values blindly; a mixed column needs provenance or carefully bounded classification. ToolAcre helps inspect samples but does not infer which service wrote each row.

Mixed magnitudes may also distort indexes and retention queries before anyone opens a row. Treat discovery as a data-integrity incident, not merely a formatting defect in one client.

The case for integer epochs — portability, sorting, arithmetic and independence from database time zone settings

An integer epoch is compact to exchange and straightforward to compare when origin, unit and width are fixed. It avoids locale-formatted text in storage and supports duration arithmetic after normalization. Those benefits come from a contract around the number, not from INTEGER by itself.

The costs appear when that contract is absent: humans cannot read the value directly, a generic client may round large integers, and a column type says nothing about seconds versus milliseconds. Add a unit suffix or schema description and validate writers at the boundary.

An integer contract should also state rounding for sub-second input. Flooring, truncating or rounding can assign boundary events to different seconds even when scale is otherwise correct.

Integer epochs offer simple numeric interchange, with trade-offs determined by the surrounding schema

A database-native temporal type can expose readable date operations and reject some invalid input, but range, time-zone semantics and client rendering vary by engine and type. The timestamp repository contains no database adapter, so it cannot rank those products or guarantee “awareness” from a generic type name.

Read the chosen engine’s current documentation and test the driver. Some clients may return strings, Date objects or zone-adjusted values. A native type reduces certain ambiguities only when the exact type and session behavior are understood; it is not a universal substitute for an application time model.

Native timestamp behavior is database-specific and must be verified in that engine

A narrow signed integer and a wide integer have different ranges, but width still does not encode scale. A BIGINT can safely hold many milliseconds while remaining semantically unnamed. Conversely, a 32-bit seconds field approaches a known boundary even though its values look ordinary today.

The workbook claimed comments are the only record, which is too absolute. Names, domain types, constraints, generated schemas and API specifications can all carry the unit. Use more than one enforceable layer. Human comments help reviewers, while code and validation prevent a writer from silently switching scale.

Field width and unit are independent schema decisions

Suppose a row created during a known 2025 deployment contains `1738578060000`. As milliseconds it becomes `2025-02-03T10:21:00.000Z`; as seconds it is outside ordinary expectations and may exceed the consumer’s range. A neighbouring row `1738578060` maps to the same instant as seconds.

That pair suggests mixed units but does not prove which writers are responsible. Group by service version, ingest path or magnitude, then verify several known events. Preserve backups and migration logs. The converter is an audit lens, not a bulk rewrite engine.

Audit several dates across the affected period. One coincidental match can be misleading, whereas a consistent producer-specific pattern supports a controlled migration rule.

Worked example: determine a suspicious legacy column’s scale from known records

Prevent recurrence by naming raw fields `created_at_s` or `created_at_ms`, parsing at one adapter and exposing a single internal instant type. Store UTC instants; apply local presentation only at user-facing edges. If a textual API value is preferable, require an explicit offset or Z.

Tests should send distinguishable values across every serialization boundary. Zero is a poor fixture because both scales agree. Assert a fixed ISO instant and round-trip it through the actual driver. That catches unit loss before two services populate one column differently for months.

During migration, reject new writes that violate the chosen contract before repairing old rows. Otherwise cleanup races an active source that continues creating mixed data.

What this does not cover — database-specific functions such as FROM_UNIXTIME and to_timestamp, which vary by engine

This article does not prescribe `FROM_UNIXTIME`, `to_timestamp` or equivalent functions. Their input units, ranges and zone interactions belong to specific engines and versions, none of which is part of the ToolAcre implementation. Copying a function name across databases can create the very ambiguity under review.

Use vendor documentation and a disposable table to prove conversions before a migration. Keep application and database transformations from both applying the same offset or factor. A single well-owned conversion is easier to test than a chain of implicit casts.

Run the database function against boundary fixtures in the same session settings as production. Session zone defaults can alter textual results even when epoch arithmetic is correct.

Takeaway: the schema is where the unit lives — and how the Unix timestamp converter helps you audit existing data by stating the unit it applied

The schema should make a timestamp’s representation unsurprising to every writer and reader. Integers can be appropriate; native temporal columns can be appropriate. An unnamed scale is not. Choose one contract, enforce it and treat conversion as an explicit boundary operation.

For legacy data, inspect samples under both units, correlate them with known events and record uncertainty. ToolAcre’s visible unit selection supports that investigation, but the final migration decision must come from provenance and the database’s actual semantics.

A schema review is complete only when writers, readers, indexes and retention jobs share the same model. Fixing the column comment alone leaves executable ambiguity intact.