Unix Timestamp (milliseconds)

Worked samples

DuckDB 1.5.5 · finetype 0.6.64

Shape

Background

Origin

The Open Group Base Specifications define the Epoch as the time zero hours, zero minutes, zero seconds, on January 1, 1970 Coordinated Universal Time (UTC).^[1] Wikipedia records that the earliest versions of Unix time had a 32-bit integer incrementing at a rate of 60 Hz.^[2] According to Wikipedia, the current epoch of 1 January 1970 00:00:00 UTC was selected arbitrarily by Unix engineers.^[2] In Unix time, every day contains exactly 86400 seconds.^[2]

An ECMAScript time value represents an instant in time to millisecond precision.^[3] ECMAScript time values do not account for UTC leap seconds.^[3] The ECMAScript specification gives time values a supported range of exactly -100,000,000 days to 100,000,000 days relative to midnight at the beginning of 1 January 1970 UTC.^[3] Java's System.currentTimeMillis() returns the difference, measured in milliseconds, between the current time and midnight, January 1, 1970 UTC.^[4] Java's documentation notes that while the unit of currentTimeMillis() is a millisecond, the granularity of the value depends on the underlying operating system and may be larger.^[4]

Use

JavaScript's Date.now() returns the number of milliseconds elapsed since the epoch.^[5] MDN notes that the precision of Date.now() may be reduced, depending on browser settings, to protect against timing attacks and fingerprinting.^[5] Elasticsearch stores dates internally as a long number representing milliseconds since the epoch.^[6] When no format is set on a date field, Elasticsearch uses strict_date_optional_time||epoch_millis.^[6] RFC 9562 defines the unix_ts_ms field of a UUIDv7 as a 48-bit big-endian unsigned number of the Unix Epoch timestamp in milliseconds.^[7]

Python's time.time() returns the time in seconds since the epoch as a floating-point number.^[8] Python's datetime.fromtimestamp() takes a POSIX timestamp such as is returned by time.time().^[9] Before converting a column of epoch numbers, confirm from the source's documentation whether they count seconds, milliseconds or microseconds. (Meridian's view) DuckDB's make_timestamp_ms() converts milliseconds since the epoch to a timestamp.^[10] DuckDB's documentation gives make_timestamp_ms(1667810584123) as returning 2022-11-07 08:43:04.123.^[10] DuckDB's epoch_ms() returns the total number of milliseconds since the epoch for a timestamp.^[10] Convert the number as UTC first, and apply any local time zone afterwards. (Meridian's view) Elasticsearch's format reference warns that lenient date_optional_time parsing can lead to unexpected results when paired with a numeric format such as epoch_millis.^[11]

Where you'll meet it

Binance's spot REST API documentation states that all time and timestamp related fields in its JSON responses are in milliseconds by default.^[12]

Sources

1. [1]Definitionsstandard
2. [2]Unix timesecondary source
3. [3]ECMAScript® 2027 Language Specificationstandard
4. [4]System (Java Platform SE 8)documentation
5. [5]Date.now() - JavaScript | MDNdocumentation
6. [6]Date field typedocumentation
7. [7]RFC 9562: Universally Unique IDentifiers (UUIDs)standard
8. [8]time — Time access and conversionsdocumentation
9. [9]datetime — Basic date and time typesdocumentation
10. [10]Timestamp Functionsdocumentation
11. [11]formatdocumentation
12. [12]General REST API Information | Binance Developer Docsorganisation

Detection notes

Use it

Schema

Aliases