Converting Unix timestamps in practice: logs, APIs and spreadsheets

Sooner or later everyone who works near software meets a number like 1756000000 in a log line, an API response or an exported CSV, and has to turn it into a time a human can act on. The conversion itself is simple. The mistakes are all in the assumptions around it.

First: seconds or milliseconds?

A Unix timestamp counts from the start of 1970 in UTC. Some systems count seconds, others milliseconds, and a few use microseconds. Telling them apart is easy by eye: a ten-digit number is seconds, a thirteen-digit number is milliseconds. Feed milliseconds into a tool expecting seconds and you land tens of thousands of years in the future, which is at least an obvious kind of wrong. The dangerous version is dividing by a thousand and losing sub-second precision you needed.

Second: the number has no time zone

This is the part worth internalising. A timestamp is an instant, not a local reading. The same number means the same moment everywhere on Earth, and a local time only appears when something displays it by applying an offset. So there is no such thing as "a timestamp in Berlin time". If two systems disagree about what a timestamp means, one of them is applying an offset it should not, usually when writing rather than when reading.

When you convert one, be explicit about which reading you want: the UTC reading, which is canonical and good for comparing systems, or a local reading for a specific place, which is what you want when you are trying to work out what a user experienced.

Third: spreadsheets do not speak Unix

Spreadsheet software counts days from its own epoch, not seconds from 1970, so a timestamp pasted into a cell is just a large number. The usual conversion divides the seconds by 86400 and adds the spreadsheet's epoch date. That formula has two traps. It silently produces a local-looking value with no zone attached, and if your locale's epoch differs the constant is wrong. If the result is out by a few hours, the formula almost certainly added a time zone offset that was never in the data.

Fourth: log timestamps are not always UTC

Well-behaved systems log in UTC. Plenty of real systems log in the server's local time, which changes when the server moves, when the operating system is reconfigured, or twice a year when the clocks go back and an hour of log lines repeats. If you are correlating two systems and events appear an hour apart, check for a clock change before you look for a bug.

A working routine

• Count the digits to establish the unit.
• Convert to a UTC reading first, because that one is unambiguous.
• Then convert to the local reading of the place you actually care about.
• Write both into the ticket or the report, and label them.

The Unix timestamp converter does this in both directions: paste a number to get the UTC and local readings, or pick a date and time to get the number back. It is the fastest way to settle an argument about what a log line actually says, which is usually what the conversion is for.