Epoch converter
| A value from today | Digits | What it is |
|---|---|---|
| 1700000000 | 10 | Unix seconds |
| 1700000000000 | 13 | Unix milliseconds — JavaScript |
| 1700000000000000 | 16 | Unix microseconds — PostgreSQL, Python |
| 1700000000000000000 | 19 | Unix nanoseconds — Go, Prometheus |
| 133444736000000000 | 18 | Windows FILETIME and LDAP, from 1601 |
| 638355968000000000 | 18 | .NET ticks, from year one |
| 45244.93 | 5 and a fraction | Excel serial, from 1899-12-30 |
| 2460000.5 | 7 and .5 | Julian Day, from 4713 BC |
A signed 32-bit integer holds seconds up to 2,147,483,647, which runs out at 03:14:07 UTC on 19 January 2038. Systems still storing timestamps that way will wrap to 1901. It is the same shape of problem as Y2K and has been known about for decades. 64-bit time handles it, and most modern systems have moved, but embedded devices are still being found.
The Unix epoch is midnight UTC on 1 January 1970, and epoch time counts seconds from it: 1000000000 was 01:46:40 UTC on 9 September 2001. The same panel reads milliseconds, microseconds and nanoseconds, Windows FILETIME from 1601, .NET ticks from year one, Excel serials from 1899 and Julian days from 4713 BC.
How to convert epoch time
Negative epoch values are perfectly valid and represent dates before 1970. Epoch −86400 is 31 December 1969. A surprising amount of software mishandles them, either rejecting the value or producing a date decades out, because the implementation assumed unsigned arithmetic. It is worth testing if you are handling historical dates and not just log timestamps.
The other thing epoch time is not is a count of elapsed seconds. Every day in Unix time is defined as exactly 86,400 seconds, so when a leap second is inserted the clock repeats a value instead of counting past it. Twenty-seven leap seconds have been added since 1972, which means the real interval between two epoch values that far apart is 27 seconds longer than the subtraction says. That is deliberate: it keeps epoch arithmetic to plain division and lets every date convert without a lookup table, and it is the reason epoch time and atomic time are different quantities. The General Conference on Weights and Measures resolved in 2022 to widen the tolerance between UTC and the Earth’s rotation by or before 2035, which ends leap-second insertions in practice; the discrepancy then stops growing and stays where it is.
Everything else on this page exists because the unit and the origin are both guesses unless someone wrote them down. Digit count settles the unit for Unix values: ten is seconds, thirteen milliseconds, sixteen microseconds, nineteen nanoseconds, and a value read one step out lands in 1970 or in the year 57000 instead of failing. Origin is harder. Windows FILETIME counts from 1 January 1601, chosen because that year opens the first complete 400-year Gregorian cycle after the 1582 reform, which makes the calendar arithmetic land on round numbers. Active Directory reuses the identical encoding, which is why an LDAP timestamp, a pwdLastSet and a lastLogonTimestamp are all FILETIME values and all need the same sentinel handling. .NET ticks use the same 100-nanosecond unit but count from year one, and at a current date both formats run to eighteen digits, so only magnitude tells them apart.
Excel counts whole days from 1899-12-30 with the time as a fraction, and it believes 1900 was a leap year because Lotus 1-2-3 did in 1983 and file compatibility was worth more than the bug; serial 60 is a day that never existed. A spreadsheet four years out came from Mac Excel and its 1904 epoch instead. PostgreSQL stores microseconds from 2000-01-01, not 1970, so a raw internal value needs the epoch shift as well as the unit. And once a value passes eighteen digits, JavaScript cannot hold it in a Number at all: the safe integer range ends at 2^53 − 1, so the last digits are silently lost unless the value is kept as a BigInt or a string.
None of these formats carries a time zone, and neither does a bare date string. The inhabited offsets span twenty-six hours, from UTC−12 to UTC+14, so a date written without a zone marker is ambiguous by that much: two systems in different places can honestly disagree about which calendar day a naked "2024-01-01" belongs to. JavaScript makes the trap concrete. new Date("2024-01-01") is parsed as UTC because it is date-only, new Date("2024-01-01T12:00:00Z") is UTC because of the Z, and new Date("2024-01-01T12:00:00") is your local time because the standard says a date-time without an offset is local. The space-separated new Date("2024-01-01 12:00") is not in the standard at all and is read as local by the engines that accept it. The same hole exists in .NET in a different shape: a DateTime carries only a Kind flag, and that flag does not survive most serialisation, which is precisely why DateTimeOffset exists.
What people use it for
- Reading a timestamp from a log or database
- Converting a date for an API request
- Decoding a thirteen-digit JavaScript timestamp
- Decoding a sixteen-digit PostgreSQL timestamp
- Decoding a nineteen-digit Go or Prometheus timestamp
- Reading a tick value out of a C# log
- Producing a .NET tick value for a test fixture
- Decoding a file or registry timestamp on Windows
- Writing an accountExpires value for Active Directory
- Reading lastLogonTimestamp out of an AD export
- Decoding a spreadsheet column that imported as numbers
- Turning a date into the serial number Excel stores
- Computing an interval for astronomy software
- Converting a historical date to a negative epoch
Questions
Midnight UTC on 1 January 1970. Epoch time counts seconds from that moment.
Ten digits is seconds, thirteen is milliseconds, for any date this century. If it decodes to 1970 you have milliseconds being read as seconds.
Sixteen for a current date. Ten is seconds, thirteen milliseconds, nineteen nanoseconds.
None. It names a moment, not a local time. Everything here is read and shown in UTC.
Yes, for dates before 1970. Epoch −86400 is 31 December 1969, and software that assumed unsigned arithmetic handles it badly.
No. Every day is fixed at 86,400 seconds, so a leap second repeats a value instead of advancing past it. Twenty-seven have been inserted since 1972.
A signed 32-bit timestamp overflows on 19 January 2038 and wraps to 1901. The failure is silent, which makes it worse than Y2K.
A tick is 100 nanoseconds, ten million to the second, and both formats count in them. Only the origin differs: Windows FILETIME starts at 1601 and .NET DateTime at year one, so reading one as the other is wrong by 1,600 years.
Because 1601 opens the first complete 400-year Gregorian cycle after the 1582 reform, and the Gregorian calendar repeats exactly on that cycle. Starting there means the leap-year arithmetic never needs a special case.
Identical encoding. Active Directory reuses the Windows format for pwdLastSet, lastLogonTimestamp, accountExpires and the rest, so anything that decodes a FILETIME decodes those, sentinels included.
No. A .NET DateTime carries a separate Kind flag, and that flag does not survive most serialisation, so two equal tick counts can mean two different instants. DateTimeOffset exists for exactly this reason and is the safer type to store.
Because a date-time with no zone marker is read as local. In JavaScript, new Date("2024-01-01") is UTC because it is date-only, new Date("2024-01-01T12:00:00Z") is UTC because of the Z, and new Date("2024-01-01T12:00:00") is your local time. The space-separated form is not in the standard at all. Since inhabited offsets span twenty-six hours, an unmarked date is ambiguous by up to that much.
"Never", not 1601, and the maximum 9223372036854775807 means the same on accountExpires. pwdLastSet is the other sentinel worth knowing: a zero there means the user must change their password at next logon rather than naming a date. All of them need special-casing, or a report shows nonsense dates in the seventeenth century.
By design. It replicates only every 9 to 14 days. The accurate attribute is lastLogon, which is per-controller and not replicated.
Subtract 621355968000000000 and divide by 10000000. That constant is the gap between the two epochs.
Lotus 1-2-3 had the bug in 1983 and Microsoft kept it for file compatibility. Serial 60 is a day that never existed.
Time of day as a fraction of a day. 0.5 is midday, 0.75 is 6 pm.
The file came from Mac Excel, which used a 1904 epoch. The workbook has a setting to switch.
Because PostgreSQL stores microseconds from 2000-01-01 internally rather than from 1970, so reading a raw value as Unix microseconds lands three decades short: a row written in 2026 comes back in 1996. There is no PostgreSQL setting in the epoch list here, and adding one would hide the arithmetic rather than explain it — add 946,684,800,000,000 to the raw value, which is the thirty years between the two origins in microseconds, and read the result as Unix microseconds. The mistake in the other direction is the well-known one: hand a Unix value to something expecting the internal form and it decodes to 2053. None of this applies to extract(epoch from ts), which already gives Unix seconds.
Not as a Number: it exceeds the safe integer range and the last digits are lost. Use BigInt, or keep it as a string. Go’s UnixNano is the usual source of a nineteen-digit value, and it has a range limit of its own: signed 64-bit nanoseconds only cover about 1678 to 2262.
A continuous count of days from noon UTC on 1 January 4713 BC, written JD in astronomy, so any Gregorian date converts to a positive seven-digit figure. It ends in .5 for a midnight date because Julian days start at noon, and subtracting 2400000.5 gives the Modified Julian Day that astronomers usually prefer.
No, and the shared name causes real confusion. A packing or lot code is an ordinal date, a number from 1 to 366 within a year. The astronomical Julian Day is the continuous count from 4713 BC, and the two are never the same figure.