Unix Timestamp Reference: Epoch Values and Conversions

Seconds or milliseconds? Count the digits

The single most common bug when handling epochs is feeding a millisecond value into a function that expects seconds, which lands you somewhere around the year 58,000. You almost never need to guess: the magnitude of the number tells you the unit. For any date in the current era, digit count alone is decisive.

If a value is 13 digits and ends in three zeros it is almost certainly milliseconds produced from a whole-second source. Java, JavaScript and Kafka default to milliseconds; C, Python, Go, PostgreSQL and the Unix shell default to seconds. JWT claims (iat, exp, nbf) are always seconds per RFC 7519, which trips up JavaScript developers constantly because Date.now() is not.

Notable epoch values

Converting epoch to date in SQL

Every engine spells this differently, and the differences are not cosmetic — they affect the return type and the timezone. The left column converts an epoch in seconds to a timestamp; the right column goes back the other way.

Three caveats that cause real incidents. PostgreSQL’s to_timestamp returns timestamp with time zone, so it renders in the client’s TimeZone setting rather than UTC — append AT TIME ZONE 'UTC' if you need a stable display. MySQL’s FROM_UNIXTIME is documented to return the value in the session time zone, so the same query gives different answers to two different clients. And on SQL Server 2017 through 2022 the number argument to DATEADD is an int, so DATEADD(MILLISECOND, 1700000000000, ...) overflows; convert milliseconds in two steps instead.

-- SQL Server: milliseconds without int overflow
SELECT DATEADD(MILLISECOND, 1700000000000 % 1000,
         DATEADD(SECOND, 1700000000000 / 1000, '19700101'));

-- PostgreSQL: force a UTC rendering
SELECT to_timestamp(1700000000) AT TIME ZONE 'UTC';

Converting in code

Excel and Google Sheets

Spreadsheets do not store epochs at all. They store a serial day number counted from 30 December 1899, so converting means dividing by the number of seconds in a day and adding the constant 25569 — the serial number of 1 January 1970. Format the result cell as a date afterwards or you will just see a decimal.

=A1/86400 + 25569          → epoch seconds to date
=A1/86400000 + 25569       → epoch milliseconds to date
=(A1 - 25569) * 86400      → date back to epoch seconds

The constant 25569 looks arbitrary but is not: it includes an off-by-one that Excel inherited deliberately. Excel treats 1900 as a leap year, which it was not, so its serial numbers are one day ahead of reality for any date after February 1900. Because 25569 was derived under the same wrong assumption, the two errors cancel and dates from 1970 onward come out correct.

The 2038 problem and other boundaries

A signed 32-bit integer stops at 2,147,483,647. A system storing Unix time that way runs out of room at 03:14:07 UTC on 19 January 2038, and the next second wraps around to December 1901. This is not hypothetical for embedded devices, older filesystems and any schema that chose INT instead of BIGINT.

The fix is unglamorous: store epochs in 64-bit columns. A signed 64-bit second count runs out roughly 292 billion years from now, which is comfortably past the point where it stops being your problem. If you are choosing a column type today, BIGINT or a native timestamp type are both fine; INT is a dated landmine.

Gotchas worth knowing

1. Unix time ignores leap seconds. POSIX requires every day to be exactly 86,400 seconds, so the 27 leap seconds inserted into UTC since 1972 are simply not counted. An epoch value is a calendar calculation, not a tally of elapsed physical seconds.
2. An epoch has no timezone. The number is always UTC-based; the timezone only enters when you format it. Two servers showing different dates for the same integer are formatting differently, not storing differently.
3. Negative epochs are legal and mean dates before 1970. Plenty of libraries and a few databases handle them badly, so test if you store birthdates this way.
4. Watch for epochs stored as strings. Sorting "9999999999" against "10000000000" lexicographically puts them in the wrong order, and the bug only appears once the digit count changes.
5. Seconds-resolution timestamps cannot express ordering within the same second. If you need to sequence events, store milliseconds or add a tiebreaker column.

Convert your own values

This table covers the values people look up repeatedly, but not the one sitting in your log file right now. The Unix Timestamp Converter on this site takes any epoch and shows it in your local timezone, in UTC and as ISO 8601 at once, auto-detects whether you pasted seconds or milliseconds, and runs the conversion in reverse from a date picker. It also keeps a live current timestamp ready to copy. Like everything here it runs entirely in your browser — the values you paste are never uploaded anywhere.