Ten digits or thirteen: which timestamp is that

A timestamp arrives as a bare number and carries no label saying what it counts. Get the unit wrong and the date lands in 1970 or tens of thousands of years from now; get the epoch wrong and it lands centuries out.

Both are guessable from the number itself, which is what makes this a five-second check rather than an investigation. Counting the digits gives the unit, and the magnitude gives the epoch.

How do I tell which unit a number is in?

By counting its digits. The magnitude of a timestamp for any date near now is distinctive enough to identify the unit at a glance.

The symptom of getting it wrong is unmistakable. A timestamp in milliseconds read as seconds produces a date tens of thousands of years in the future; one in seconds read as milliseconds produces a date in January 1970.

Both are so far out that they are easy to spot, which is the one mercy of this particular bug. The dangerous version is a system that silently clamps the result to a valid range instead.

Why is the count not the true number of seconds?

Because it ignores leap seconds. A leap second is inserted to keep clocks aligned with the earth’s rotation, and Unix time does not have a representation for one — so the count either repeats a value or skips forward.

That means the difference between two Unix timestamps is not exactly the elapsed physical time between them. For almost every purpose the discrepancy is irrelevant; for anything measuring intervals precisely across years it is not.

The upside is that the arithmetic stays simple. Every day is exactly 86,400 seconds in this scheme, which is what makes conversion a division rather than a table lookup.

Which epoch is a Windows timestamp using?

A different one, at a different resolution. Some Microsoft and directory formats count 100-nanosecond intervals from 1 January 1601, which produces the very large eighteen-digit numbers seen in account records.

The choice of 1601 is not arbitrary. The Gregorian calendar repeats on a 400-year cycle, and 1601 is the start of the first complete cycle after its adoption — so date arithmetic within the era needs no special cases.

The practical consequence is that these numbers cannot be treated as Unix time at any scale. The offset between the two epochs is a fixed constant, and converting is a subtraction followed by a division.

Does a timestamp have a time zone?

No, and that is the point of it. Unix time is a count from a fixed instant in UTC, so the same number means the same moment everywhere on earth.

Time zones enter only when the number is turned into a date and a clock time for a person to read. That is a formatting step, and it is where a timestamp acquires an offset rather than where it had one.

Storing an instant as a timestamp and applying a zone at display time is the arrangement that avoids nearly every date bug. Storing a local time and hoping to remember which zone it was in is the arrangement that causes them.

What does a nonsense result tell you?

Which mistake was made. A date in early 1970 means a value in seconds was read as milliseconds, so it was divided by a thousand somewhere it should not have been; a date tens of thousands of years out means the reverse.

A date in the 1600s means a Windows-era value was read against the Unix epoch. A date around 1900 means something else entirely — usually a spreadsheet serial, or a signed 32-bit count that has wrapped, which is the 2038 problem reaching a value early.

None of these is a subtle error, which is the one convenience here. A timestamp misread by unit or epoch is wrong by centuries rather than by hours.

What is a spreadsheet date serial?

A day count with its own epoch and its own famous bug. Spreadsheets store a date as a number of days since the start of 1900, with the time of day as the fraction after the decimal point — so a whole number is midnight and 0.5 is noon.

The bug is that the count treats 1900 as a leap year, which it was not. A non-existent 29 February sits in the series, so every date after it is off by one relative to a correct calendar — and it was left in deliberately for compatibility with an earlier spreadsheet that had the same error.

That is why converting a serial by hand gives an answer one day out for early dates and the right answer for modern ones. Any conversion has to know which side of the phantom day it is working on.

Why store an instant rather than a date?

Because a date and a clock time are not an instant until a zone is attached, and a zone can change. Governments adjust their offsets and their daylight rules with little notice, so a future local time is a promise about a rule that may not hold.

That is the one case where storing a timestamp is wrong. A meeting at nine in the morning next March is a local time, and converting it to an instant now bakes in today’s rules — if the rules change, the meeting moves.

The distinction is between an event that happened and an appointment that will. A log entry is an instant and belongs as a timestamp; a scheduled appointment is a local time plus a zone name, and the instant is computed when it is needed.

Questions people ask

Can a timestamp be negative? Yes. Negative values are dates before 1970, and some systems handle them badly.

Is the epoch the same everywhere? For Unix time, yes. Other systems use their own, which is why the offsets exist.

Why does my timestamp show yesterday? A zone applied at display time, or a zone not applied when it should have been.

How do I know the epoch without trying it? By magnitude. A ten-digit value is Unix seconds; an eighteen-digit one is almost certainly a 100-nanosecond count from 1601.

Convert rather than assume. The Unix timestamp converter and its simple version handle seconds and milliseconds, the epoch converter goes in both directions, microseconds and nanoseconds cover the higher resolutions, and the LDAP timestamp converter, Windows FILETIME converter and .NET ticks converter handle the 1601 epoch, milliseconds to date covers the thirteen-digit form directly, and the Excel date serial converter deals with the day count and its phantom leap day. Going the other way — from a date you already have to the number a system expects — are date to timestamp, date to Excel serial, date to Windows FILETIME and date to .NET ticks.