Skip to content

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 +%s command, PHP time(), PostgreSQL EXTRACT(EPOCH FROM …), most REST APIs. Python’s time.time() gives seconds with a fraction.
  • Milliseconds: JavaScript Date.now(), Java System.currentTimeMillis(), many analytics and logging exports.
  • Microseconds: BigQuery UNIX_MICROS() and other databases that keep six decimal places.
  • Nanoseconds: Go time.Now().UnixNano(), Python time.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:

UnitDigits29 June 2024, 00:00 UTC
Seconds101719619200
Milliseconds131719619200000
Microseconds161719619200000000
Nanoseconds191719619200000000000

Fix it on Date And Time Converter

  1. 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.
  2. 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.
  3. 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.
Worked example: 13 digits: milliseconds, detected from the length.
Input
1719619200000 read as Unix Timestamp (ms) (detected)
Result
2024-06-29T00:00:00.000Z YYYY-MM-DDTHH:mm:ss.SSSZ · YYYY-MM-DDTHH:mm:ss.SSSZ

Open in the converter: 1719619200000 → 2024-06-29T00:00:00.000Z

Worked example: 16 digits: microseconds. The same moment, three digits longer.
Input
1719619200000000 read as Unix Timestamp (µs) (detected)
Result
2024-06-29T00:00:00.000Z YYYY-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 MySQL FROM_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.