MeridianMERIDIAN

Unix Timestamp (seconds)

datetime.epoch.unix_seconds

Seconds since 1970-01-01 00:00:00 UTC. The standard epoch representation used in most programming languages and systems.

Worked samples

DuckDB 1.5.5 · finetype 0.6.64

ValueAs TIMESTAMP
17053122002024-01-15 09:50:00+00
15778367992019-12-31 23:59:59+00
10000000002001-09-09 01:46:40+00
Standard

Shape

Pattern
^\d{10}$
ValueLengthShape
170531220010matches
157783679910matches
100000000010matches
1705312209rejected: one character short

Background

Origin

The earliest versions of Unix time had a 32-bit integer incrementing at a rate of 60 Hz.[1] Midnight on 1 January 1971 and 1 January 1972 were both used as epochs during the early development of Unix.[1] The precision of Unix time was changed to count in seconds in order to avoid short-term overflow.[1] 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 seconds since the Epoch as a value that approximates the number of seconds that have elapsed since the Epoch.[2] The POSIX definition relates a Coordinated Universal Time name to seconds since the Epoch through a C-language expression built from the seconds, minutes, hours, day of the year and year.[2] POSIX states that, as represented in seconds since the Epoch, each and every day shall be accounted for by exactly 86400 seconds.[2] POSIX leaves the relationship undefined if the year is before 1970 or the value is negative.[2] The 2001 edition of POSIX.1 rectified a faulty leap year rule in the definition of Unix time.[1]

In Unix time, every day contains exactly 86400 seconds.[1] Unix time differs from both Coordinated Universal Time and International Atomic Time.[1] Python's documentation says leap seconds are excluded from seconds since the epoch on all POSIX-compliant platforms.[3]

Use

Unix time is used as a mechanism for storing timestamps in a number of file systems, file formats and databases.[1] Python's time.time() returns the time in seconds since the epoch as a floating-point number.[3] PostgreSQL's to_timestamp(double precision) converts a Unix epoch value, in seconds since 1970-01-01 00:00:00+00, to a timestamp with time zone.[4] DuckDB's to_timestamp function converts seconds since the epoch to a timestamp with time zone.[5] RFC 7519 defines the JSON Web Token NumericDate as a JSON numeric value representing the number of seconds from 1970-01-01T00:00:00Z UTC, ignoring leap seconds.[6]

JavaScript Date objects count milliseconds, not seconds, since the midnight at the beginning of January 1, 1970, UTC.[7] Treat a seconds value as an instant in UTC, and apply a local time zone only when you display it. (Meridian's view)

Unix time passed 1000000000 seconds on 2001-09-09T01:46:40Z.[1] A signed 32-bit value of Unix time cannot represent dates after 19 January 2038 at 03:14:07 UTC, which is known as the year 2038 problem.[1] If a source system stores these values in a 32-bit integer column, check what it does with dates beyond 2038 before you trust them. (Meridian's view)

Where you'll meet it

Stripe's API reference describes the created attribute of a Charge as measured in seconds since the Unix epoch.[8] GitHub's REST API documentation gives the x-ratelimit-reset header in UTC epoch seconds.[9] The OpenSky Network's REST API reports time_position and last_contact in state vectors as Unix timestamps in seconds.[10]

Sources

  1. [1]Unix timesecondary source
  2. [2]General Conceptsstandard
  3. [3]time — Time access and conversionsdocumentation
  4. [4]9.9. Date/Time Functions and Operatorsdocumentation
  5. [5]Timestamp with Time Zone Functionsdocumentation
  6. [6]RFC 7519: JSON Web Token (JWT)standard
  7. [7]Date - JavaScript | MDNdocumentation
  8. [8]The Charge objectorganisation
  9. [9]Rate limits for the REST APIorganisation
  10. [10]OpenSky REST APIorganisation

Detection notes

v1 names: datetime.unix_timestamp, datetime.timestamp. Tier 0 is BIGINT (looks like a number) but broad_type output is TIMESTAMP after transformation.

Use it

CLI · DuckDB
$ finetype infer -i "1705312200" --mode column
→ datetime.epoch.unix_seconds

SELECT ft_infer('1705312200');
-- → 'datetime.epoch.unix_seconds'
Cast expression
to_timestamp({col}::BIGINT)

Schema

https://meridian.online/schemas/datetime.epoch.unix_seconds · Frictionless type integer

finetype taxonomy datetime.epoch.unix_seconds -o json-schema

Aliases

unix_timestamptimestamp

Type Registry