Precision & resolution
Seconds, millis, micros, nanos — and the bugs born when systems disagree.
Resolution is the smallest unit a representation can express. Different systems carry different resolutions, and mismatches between them silently truncate or distort values.
Classic Unix time (time_t) is whole seconds. JavaScript’s Date stores milliseconds (integer). PostgreSQL’s timestamp stores microseconds. Most modern OS APIs can return nanoseconds (though hardware precision may be lower). When you move a value between systems, you must be explicit about whether to truncate (floor), round, or raise an error.
Precision refers to how meaningful those digits actually are — a nanosecond-resolution value read from a low-frequency timer has high resolution but low precision. Resolution is a property of the type; precision is a property of the measurement.
Mismatches cause bugs
The most common class of bug: a value is stored in a coarser-grained system (e.g. a Unix second) and later compared to a finer-grained value (e.g. a JavaScript millisecond timestamp). The comparison fails or produces unexpected results because the finer value has sub-second information that the stored value lost.
Pitfall: Storing a timestamp as a Unix second integer, then comparing it to Date.now() (milliseconds since epoch) without dividing by 1000. The values differ by three orders of magnitude and the comparison is silently wrong.
Go deeper: the Unix epoch, integer overflow, and database precision
The Unix epoch is 1970-01-01T00:00:00Z. A 32-bit signed Unix timestamp wraps at 2147483647 — which is 2038-01-19T03:14:07Z, the so-called “Year 2038 problem”. Systems still using 32-bit time_t will break on that date. 64-bit timestamps extend the range to approximately 292 billion years in either direction from the epoch.
PostgreSQL’s timestamp and timestamptz types store values as microseconds since 2000-01-01, which differs from the Unix epoch. When using PostgreSQL’s extract(epoch from ...), the result is a floating-point number of seconds since the Unix epoch; floating-point representation loses sub-microsecond precision for recent dates (the double’s resolution is roughly ±0.2–0.4 µs for dates in the 2000s).
MySQL’s DATETIME type has 1-second resolution by default; DATETIME(6) adds up to microsecond precision. SQLite stores timestamps as text or real numbers — see MySQL & SQLite.
For the Unix epoch and how systems count time at the second level, see Unix time and Instant vs civil time.