MeridianMERIDIAN

Unix Epoch Nanoseconds

datetime.timestamp.epoch_nanoseconds

Nanoseconds since Unix epoch (1970-01-01T00:00:00Z). 19-digit integer. OpenTelemetry time_unix_nano, Go time.UnixNano(). Distinct from other epoch formats by digit count (19 vs 10/13/16).

Worked samples

DuckDB 1.5.5 · finetype 0.6.64

ValueAs TIMESTAMP
17053254000000000002024-01-15 13:30:00
17040671990000000002023-12-31 23:59:59
17172285300000000002024-06-01 07:55:30
Standard

Shape

Pattern
^\d{19}$
ValueLengthShape
170532540000000000019matches
170406719900000000019matches
171722853000000000019matches
17053254000000000018rejected: one character short

Background

Origin

The Unix time 0 is exactly midnight UTC on 1 January 1970.[1] In Unix time, every day contains exactly 86400 seconds.[1] Unix time values are sometimes stored with higher granularity, such as microseconds or nanoseconds.[1] Go's time package documents UnixNano as returning a time as the number of nanoseconds elapsed since January 1, 1970 UTC.[2] Go's documentation says the result of UnixNano is undefined for a date before the year 1678 or after 2262, because the value cannot be represented by an int64.[2]

FineType's label definition gives the type the alias unix_nano.[3]

Use

OpenTelemetry's Metrics Data Model records the time a value was sampled in a field called time_unix_nano.[4] The OpenTelemetry Metrics Data Model specifies histogram time values as nanoseconds since the UNIX Epoch (00:00:00 UTC on 1 January 1970).[4] OpenTelemetry's log record protocol stores time_unix_nano as a fixed64 field holding UNIX Epoch time in nanoseconds since 00:00:00 UTC on 1 January 1970.[5] In OpenTelemetry's log record protocol, a time_unix_nano value of 0 indicates an unknown or missing timestamp.[5] OpenTelemetry's trace protocol gives a span's start_time_unix_nano and end_time_unix_nano as UNIX Epoch time in nanoseconds.[6] InfluxDB's line protocol takes a Unix timestamp whose default precision is nanoseconds.[7] Wikipedia's article on Unix time says APFS and ext4 both use Unix time in nanoseconds for file timestamps.[1]

Treat a zero in an OpenTelemetry export as a missing value rather than as midnight on 1 January 1970. (Meridian's view) Keep these values as 64-bit integers or as text until they are converted, rather than as floating-point numbers. (Meridian's view)

DuckDB's make_timestamp_ns function converts nanoseconds since the epoch to a timestamp.[8] DuckDB's documentation gives make_timestamp_ns(1667810584123456789) as returning 2022-11-07 08:43:04.123456789.[8] DuckDB's single-argument make_timestamp function expects microseconds since the epoch, not nanoseconds.[8] pandas converts integer epoch times with to_datetime, and its default unit is nanoseconds.[9] The pandas documentation says a 64-bit integer at nanosecond resolution can span only approximately 584 years.[9] The pandas documentation gives Timestamp.max at nanosecond resolution as 2262-04-11 23:47:16.854775807.[9]

Before parsing, check that the column's digit count and the dates it decodes to fit what you expect, so that a column in another unit is not decoded as nanoseconds. (Meridian's view) Decoding a few values by hand and comparing them with a known event in the data is the quickest way to confirm the unit. (Meridian's view)

Sources

  1. [1]Unix timesecondary source
  2. [2]time package - time - Go Packagesdocumentation
  3. [3]finetype/labels/definitions_datetime.yamlFineType
  4. [4]Metrics Data Modeldocumentation
  5. [5]logs.protodocumentation
  6. [6]trace.protodocumentation
  7. [7]Line protocol | InfluxDB OSS v2 Documentationdocumentation
  8. [8]Timestamp Functionsdocumentation
  9. [9]Time series / date functionalitydocumentation

Detection notes

New in format coverage expansion. OpenTelemetry, Go time.UnixNano(). Transform casts the 19-digit text to BIGINT then converts via DuckDB make_timestamp_ns() — input is VARCHAR storage, so epoch_ns() (which expects a TIMESTAMP/TIME) has no binder overload and aborts validate.

Use it

CLI · DuckDB
$ finetype infer -i "1705325400000000000" --mode column
→ datetime.timestamp.epoch_nanoseconds

SELECT ft_infer('1705325400000000000');
-- → 'datetime.timestamp.epoch_nanoseconds'
Cast expression
make_timestamp_ns({col}::BIGINT)

Schema

https://meridian.online/schemas/datetime.timestamp.epoch_nanoseconds · Frictionless type integer

finetype taxonomy datetime.timestamp.epoch_nanoseconds -o json-schema

Aliases

unix_nano

Type Registry