How-to
How to Tell Unix Seconds From Milliseconds
A Unix timestamp can count seconds, milliseconds, microseconds or nanoseconds. Read the unit from the digits, convert it, and fix it in Excel or code.
The problem
“I have a number like 1719619200000 from an API or a database column. Is it seconds or milliseconds? The wrong guess gives me a date in 1970 or thousands of years from now.”
New to this? Read the lesson on Unix time first.
Why it happens
A Unix timestamp counts time since 1 January 1970 at midnight UTC, but nothing in the number says in what unit. Each platform picked its own:
- Seconds: the Unix
date +%scommand, PHPtime(), PostgreSQLEXTRACT(EPOCH FROM …), most REST APIs. Python’stime.time()gives seconds with a fraction. - Milliseconds: JavaScript
Date.now(), JavaSystem.currentTimeMillis(), many analytics and logging exports. - Microseconds: BigQuery
UNIX_MICROS()and other databases that keep six decimal places. - Nanoseconds: Go
time.Now().UnixNano(), Pythontime.time_ns(), ClickHouse and trace formats.
A value moves between these systems as a bare integer. Read in the wrong unit, a millisecond value lands thousands of years in the future, and a second value read as milliseconds lands in January 1970.
The digits give the unit away. For dates between 2001 and 2286, every unit has a fixed length:
| Unit | Digits | 29 June 2024, 00:00 UTC |
|---|---|---|
| Seconds | 10 | 1719619200 |
| Milliseconds | 13 | 1719619200000 |
| Microseconds | 16 | 1719619200000000 |
| Nanoseconds | 19 | 1719619200000000000 |
Fix it on Date And Time Converter
- Paste the number into the converter. It reads the unit from the number of digits and names the format it chose (“Unix Timestamp (ms)”, for example) beside the value.
- Check that reading. A short value, such as one written before 2001 or a small test number, can fit more than one unit; if the converter guessed wrong, choose the unit yourself in the source format list and the result changes at once.
- Pick the format you want as the target, for example ISO 8601 or
YYYY-MM-DD HH:mm:ss, and the zone to show it in. A Unix value is always UTC; the zone only changes how the moment is written.
- Input
1719619200000read as Unix Timestamp (ms) (detected)- Result
2024-06-29T00:00:00.000ZYYYY-MM-DDTHH:mm:ss.SSSZ · YYYY-MM-DDTHH:mm:ss.SSSZ
Open in the converter: 1719619200000 → 2024-06-29T00:00:00.000Z
- Input
1719619200000000read as Unix Timestamp (µs) (detected)- Result
2024-06-29T00:00:00.000ZYYYY-MM-DDTHH:mm:ss.SSSZ · YYYY-MM-DDTHH:mm:ss.SSSZ
Open in the converter: 1719619200000000 → 2024-06-29T00:00:00.000Z
In Excel, Google Sheets or code
Divide by the number of units in a day, then add the day count of 1 January 1970. In Excel, with the value in A1, format the result cell as yyyy-mm-dd hh:mm:ss:
Seconds: =A1/86400+DATE(1970,1,1)
Milliseconds: =A1/86400000+DATE(1970,1,1)
Microseconds: =A1/86400000000+DATE(1970,1,1) A 16-digit microsecond value is longer than the 15 digits Excel keeps in a number, so if Excel already stored it as a number its last digit is gone. Import that column as Text and keep the first 13 digits, which are the milliseconds: =VALUE(LEFT(A1,13))/86400000+DATE(1970,1,1).
The result is UTC wall time. Google Sheets has a function for it: =EPOCHTODATE(A1, 2), where the second argument is 1 for seconds, 2 for milliseconds and 3 for microseconds.
In code, convert to the unit the language expects instead of guessing:
// JavaScript: Date wants milliseconds
new Date(seconds * 1000).toISOString();
new Date(Number(nanos / 1000000n)).toISOString(); // BigInt nanoseconds
# Python: fromtimestamp wants seconds
from datetime import datetime, timezone
datetime.fromtimestamp(ms / 1000, tz=timezone.utc)
-- PostgreSQL: to_timestamp wants seconds
SELECT to_timestamp(1719619200000 / 1000.0); Pitfalls
- Excel and long numbers. Excel keeps 15 significant digits and shows long values in scientific notation (1.71962E+12). A microsecond (16 digits) or nanosecond (19 digits) value pasted into Excel has already lost its last digits, so convert it before it goes into a spreadsheet.
- Floating point. A nanosecond value is larger than a JavaScript number can hold exactly (the limit,
Number.MAX_SAFE_INTEGER, is about 9.0 × 1015). Parse it as a string or BigInt, as above. Microsecond values stay below that limit until the year 2255. - Guessing by size alone. 86400 is one day after the epoch in seconds but under a minute and a half in milliseconds. A rule such as “more than 12 digits means milliseconds” works for current dates only; say the unit in your API or column name (
created_at_ms). - Local-time conversions.
datetime.fromtimestamp(s)without a zone returns the local time of the machine running it, and MySQLFROM_UNIXTIME()returns the session’s time zone (by default the server’s). The number was UTC; the shift comes from the function.
Questions
How many digits does a Unix timestamp in seconds have?
Ten, for every moment from 9 September 2001 to 20 November 2286. Moments from 3 March 1973 to 9 September 2001 have nine digits, and moments before 1970 are negative numbers.
Why do I have to multiply by 1000 in JavaScript?
A JavaScript Date counts milliseconds: Date.now() returns them and new Date(n) expects them. Most Unix tools and many APIs give seconds, so new Date(1719619200) lands on 20 January 1970 instead of 29 June 2024.
What about a number with a decimal point?
A value such as 1719619200.5 is seconds with a fraction, which Python and PostgreSQL print. The converter reads it as seconds and keeps the fraction to the millisecond.
Is an 18-digit number a Unix timestamp?
Usually not. .NET ticks count 100-nanosecond steps from the year 1 and have 18 digits today: 638552160000000000 is 29 June 2024. This site does not convert ticks, and it does not pass one off as a Unix value.