# Unix Timestamp (microseconds) Key: `datetime.epoch.unix_microseconds` Microseconds since epoch. Used in high-frequency trading, distributed tracing (Jaeger, Zipkin), and InfluxDB. Page: Samples: `1705312200000000`, `1577836799999999` ## Background ### Origin Unix time originated as the system time of Unix operating systems.[[1]](https://en.wikipedia.org/wiki/Unix_time) 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]](https://en.wikipedia.org/wiki/Unix_time) 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]](https://en.wikipedia.org/wiki/Unix_time) POSIX defines the Epoch as the time zero hours, zero minutes, zero seconds, on January 1, 1970 Coordinated Universal Time (UTC).[[2]](https://pubs.opengroup.org/onlinepubs/9699919799/basedefs/V1_chap03.html) POSIX's sys/time.h header defines a timeval structure whose tv\_sec member holds seconds and whose tv\_usec member holds microseconds.[[3]](https://pubs.opengroup.org/onlinepubs/9699919799/basedefs/sys_time.h.html) Wikipedia notes that in modern computing, Unix time values are sometimes stored with higher granularity, such as microseconds or nanoseconds.[[1]](https://en.wikipedia.org/wiki/Unix_time) 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]](https://pubs.opengroup.org/onlinepubs/9699919799/basedefs/V1_chap04.html) Unix time differs from both Coordinated Universal Time (UTC) and International Atomic Time (TAI) in its handling of leap seconds.[[1]](https://en.wikipedia.org/wiki/Unix_time) 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]](https://github.com/openzipkin/zipkin-api/blob/master/zipkin2-api.yaml) The Zipkin API specification gives the example that 1502787600000000 corresponds to 2017-08-15 09:00 UTC.[[5]](https://github.com/openzipkin/zipkin-api/blob/master/zipkin2-api.yaml) 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]](https://github.com/openzipkin/zipkin-api/blob/master/zipkin2-api.yaml) 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]](https://github.com/openzipkin/zipkin-api/blob/master/zipkin2-api.yaml) 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]](https://github.com/apache/parquet-format/blob/master/LogicalTypes.md) 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]](https://github.com/apache/parquet-format/blob/master/LogicalTypes.md) The Parquet specification says a TIMESTAMP with isAdjustedToUTC=true unambiguously identifies a single instant on the time-line.[[6]](https://github.com/apache/parquet-format/blob/master/LogicalTypes.md) The Parquet specification says a value of the TIMESTAMP type does not necessarily correspond to a single instant on the time-line.[[6]](https://github.com/apache/parquet-format/blob/master/LogicalTypes.md) 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]](https://github.com/apache/parquet-format/blob/master/LogicalTypes.md) BigQuery's UNIX\_MICROS function returns the number of microseconds since 1970-01-01 00:00:00 UTC.[[7]](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/timestamp_functions) BigQuery's documentation shows UNIX\_MICROS(TIMESTAMP "2008-12-25 15:30:00+00") returning 1230219000000000.[[7]](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/timestamp_functions) 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]](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/timestamp_functions) DuckDB's epoch\_us function returns the total number of microseconds since the epoch.[[8]](https://duckdb.org/docs/current/sql/functions/timestamp) DuckDB's make\_timestamp function, given a single microseconds argument, converts microseconds since the epoch to a timestamp.[[8]](https://duckdb.org/docs/current/sql/functions/timestamp) DuckDB's documentation shows make\_timestamp(1667810584123456) returning 2022-11-07 08:43:04.123456.[[8]](https://duckdb.org/docs/current/sql/functions/timestamp) PostgreSQL's to\_timestamp(double precision) function converts Unix epoch seconds, not microseconds, to a timestamp with time zone.[[9]](https://www.postgresql.org/docs/current/functions-datetime.html) 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]](https://docs.influxdata.com/influxdb/v2/reference/glossary/) InfluxDB's glossary writes 2020-01-01T00:00:00Z UTC as 1577836800000000 at microsecond precision and as 1577836800000 at millisecond precision.[[10]](https://docs.influxdata.com/influxdb/v2/reference/glossary/) InfluxDB line protocol uses nanoseconds as its default timestamp precision.[[11]](https://docs.influxdata.com/influxdb/v2/reference/syntax/line-protocol/) 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. [Unix time - Wikipedia](https://en.wikipedia.org/wiki/Unix_time) — secondary source 2. [Definitions](https://pubs.opengroup.org/onlinepubs/9699919799/basedefs/V1_chap03.html) — standard 3. [\](https://pubs.opengroup.org/onlinepubs/9699919799/basedefs/sys_time.h.html) — standard 4. [General Concepts](https://pubs.opengroup.org/onlinepubs/9699919799/basedefs/V1_chap04.html) — standard 5. [zipkin-api/zipkin2-api.yaml at master · openzipkin/zipkin-api · GitHub](https://github.com/openzipkin/zipkin-api/blob/master/zipkin2-api.yaml) — documentation 6. [parquet-format/LogicalTypes.md at master · apache/parquet-format · GitHub](https://github.com/apache/parquet-format/blob/master/LogicalTypes.md) — documentation 7. [Timestamp functions \| BigQuery \| Google Cloud Documentation](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/timestamp_functions) — documentation 8. [Timestamp Functions – DuckDB](https://duckdb.org/docs/current/sql/functions/timestamp) — documentation 9. [PostgreSQL: Documentation: 18: 9.9. Date/Time Functions and Operators](https://www.postgresql.org/docs/current/functions-datetime.html) — documentation 10. [Glossary \| InfluxDB OSS v2 Documentation](https://docs.influxdata.com/influxdb/v2/reference/glossary/) — documentation 11. [Line protocol \| InfluxDB OSS v2 Documentation](https://docs.influxdata.com/influxdb/v2/reference/syntax/line-protocol/) — documentation