Unix Timestamp (microseconds)

Worked samples

DuckDB 1.5.5 · finetype 0.6.64

Shape

Background

Origin

Unix time originated as the system time of Unix operating systems.^[1] The earliest versions of Unix time used a 32-bit integer incrementing at a rate of 60 Hz, the rate of the system clock on early Unix hardware.^[1] Wikipedia reports that the current epoch of 1 January 1970 00:00:00 UTC was selected arbitrarily by Unix engineers because it was considered a convenient date to work with.^[1] POSIX defines the Epoch as the time zero hours, zero minutes, zero seconds, on January 1, 1970 Coordinated Universal Time (UTC).^[2]

POSIX's sys/time.h header defines a timeval structure whose tv_sec member holds seconds and whose tv_usec member holds microseconds.^[3] Wikipedia notes that in modern computing, Unix time values are sometimes stored with higher granularity, such as microseconds or nanoseconds.^[1] A microsecond timestamp is best read as ordinary Unix time with six more digits of precision, not as a separate clock. (Meridian's view)

POSIX states that, as represented in seconds since the Epoch, each and every day shall be accounted for by exactly 86400 seconds.^[4] Unix time differs from both Coordinated Universal Time (UTC) and International Atomic Time (TAI) in its handling of leap seconds.^[1] Treat the microsecond digits as precision within a second, not as proof that the count is accurate across leap seconds. (Meridian's view)

Use

The Zipkin API specification describes a span's timestamp as epoch microseconds of the start of the span.^[5] The Zipkin API specification gives the example that 1502787600000000 corresponds to 2017-08-15 09:00 UTC.^[5] The Zipkin API specification says the timestamp should be set using the most precise value possible, for example gettimeofday or multiplying epoch millis by 1000.^[5] The Zipkin API specification notes that its endTs and lookback query parameters are in milliseconds, as opposed to microseconds, the grain of Span.timestamp.^[5] A single system can mix units like this, so check each field's unit separately rather than assuming one unit per source. (Meridian's view)

Apache Parquet's TIMESTAMP logical type takes a unit parameter that must be one of MILLIS, MICROS or NANOS.^[6] For the int64 carrier, the Parquet specification says a TIMESTAMP value is a signed 64-bit integer count of units since the Unix epoch.^[6] The Parquet specification says a TIMESTAMP with isAdjustedToUTC=true unambiguously identifies a single instant on the time-line.^[6] The Parquet specification says a value of the TIMESTAMP type does not necessarily correspond to a single instant on the time-line.^[6] When a microsecond count comes out of a Parquet file, check whether the column was marked as adjusted to UTC before treating it as an instant. (Meridian's view) The Parquet specification describes TIMESTAMP_MICROS as the deprecated ConvertedType counterpart of a TIMESTAMP logical type that is UTC normalized and has MICROS precision.^[6]

BigQuery's UNIX_MICROS function returns the number of microseconds since 1970-01-01 00:00:00 UTC.^[7] BigQuery's documentation shows UNIX_MICROS(TIMESTAMP "2008-12-25 15:30:00+00") returning 1230219000000000.^[7] BigQuery's TIMESTAMP_MICROS function interprets an integer as the number of microseconds since 1970-01-01 00:00:00 UTC and returns a timestamp.^[7] DuckDB's epoch_us function returns the total number of microseconds since the epoch.^[8] DuckDB's make_timestamp function, given a single microseconds argument, converts microseconds since the epoch to a timestamp.^[8] DuckDB's documentation shows make_timestamp(1667810584123456) returning 2022-11-07 08:43:04.123456.^[8] PostgreSQL's to_timestamp(double precision) function converts Unix epoch seconds, not microseconds, to a timestamp with time zone.^[9] In a database whose epoch conversion expects seconds, divide a microsecond count by 1,000,000 first, and check that the division keeps the fractional part. (Meridian's view)

InfluxDB supports Unix timestamps at nanosecond, microsecond, millisecond and second precision.^[10] InfluxDB's glossary writes 2020-01-01T00:00:00Z UTC as 1577836800000000 at microsecond precision and as 1577836800000 at millisecond precision.^[10] InfluxDB line protocol uses nanoseconds as its default timestamp precision.^[11] For a column of present-day dates, the number of digits is often the quickest clue to whether an integer counts seconds, milliseconds, microseconds or nanoseconds. (Meridian's view)

Sources

1. [1]Unix time - Wikipediasecondary source
2. [2]Definitionsstandard
3. [3]<sys/time.h>standard
4. [4]General Conceptsstandard
5. [5]zipkin-api/zipkin2-api.yaml at master · openzipkin/zipkin-api · GitHubdocumentation
6. [6]parquet-format/LogicalTypes.md at master · apache/parquet-format · GitHubdocumentation
7. [7]Timestamp functions | BigQuery | Google Cloud Documentationdocumentation
8. [8]Timestamp Functions – DuckDBdocumentation
9. [9]PostgreSQL: Documentation: 18: 9.9. Date/Time Functions and Operatorsdocumentation
10. [10]Glossary | InfluxDB OSS v2 Documentationdocumentation
11. [11]Line protocol | InfluxDB OSS v2 Documentationdocumentation

Detection notes

Use it

Schema