Shape / columns · All operations

Convert to and from epoch numbers

A variant of Parse a date or time

Turn a Unix epoch number into a timestamp, or a timestamp into one.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

The DuckDB step

Turn an epoch number into a timestamp and back

SQL
CREATE TEMP TABLE result AS
SELECT to_timestamp(1700000000) AT TIME ZONE 'UTC' AS from_seconds, epoch_ms(1700000000000) AS from_millis,
       epoch(TIMESTAMP '2023-11-14 22:13:20') AS to_seconds, epoch_ms(TIMESTAMP '2023-11-14 22:13:20') AS to_millis;
SELECT * FROM result;
SELECT CASE WHEN ((SELECT from_seconds = TIMESTAMP '2023-11-14 22:13:20' AND from_millis = TIMESTAMP '2023-11-14 22:13:20' AND to_seconds = 1700000000 AND to_millis = 1700000000000 FROM result)) THEN 'as expected' ELSE error('1700000000 was not 2023-11-14 22:13:20 in both directions') END AS verdict;
  • The statement builds its own data.
  • It needs nothing installed beyond DuckDB itself.
  • The statement checks its own result and raises an error when it is wrong: “1700000000 was not 2023-11-14 22:13:20 in both directions”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

What other tools call it

Meridian's classification; an independent blind rating agreed on 95.7% of 324 rows (variant, version 3).

Duckle (1)
  • Epoch Convertread 2026-09-25

    convert between timestamp and Unix epoch seconds

Ibis (2)
KNIME Analytics Platform (1)
Polars (1)
  • from_epochread 2026-09-27

    Turn a Unix epoch number into a date or datetime

RapidMiner Studio (1)
  • Numerical to Dateread 2026-09-27

    Convert milliseconds since epoch into date-time values

Background

Unix time counts the non-leap seconds elapsed since 00:00:00 Coordinated Universal Time (UTC) on 1 January 1970, the Unix epoch.[1] A Unix time number is easily converted back into a UTC time: its quotient by 86400 is the days since the epoch, and the remainder the seconds since midnight.[1] In DuckDB, the epoch function goes the other way, returning a timestamp's total number of seconds since the epoch.[2]

When to use it

Unix time values are sometimes stored with higher granularity, such as microseconds or nanoseconds.[1] JavaScript's Date library stores timestamps in milliseconds since the Unix epoch.[1] In DuckDB, to_timestamp reads a number as seconds since the epoch, make_timestamp_ms reads milliseconds and make_timestamp_ns reads nanoseconds.[3][2] Confirm whether a column counts seconds, milliseconds, microseconds or nanoseconds before converting it, and check a few converted values against dates you already know. (Meridian's view)

Unix time is sometimes called epoch time, which can mislead, because the Unix epoch is not the only epoch used by time systems.[1] In Excel's 1900 date system, a date is stored as a serial number of days elapsed since January 1, 1900, so July 5, 2011 becomes 40729.[4] Excel's 1904 date system counts from January 1, 1904, and a date's serial number in it is always 1,462 days smaller than in the 1900 system.[4] Before reading a column of whole numbers as Unix seconds, rule out a spreadsheet's day count. (Meridian's view)

Systems that store Unix time in a signed 32-bit integer cannot represent times after 03:14:07 UTC on 19 January 2038.[5]

The method

Unix time originated as the system time of Unix operating systems and has come to be widely used in other operating systems, file systems, programming languages and databases.[1] The earliest versions of Unix time used a 32-bit integer incrementing at 60 Hz, and midnight on 1 January 1971 and 1 January 1972 were both used as epochs during Unix's early development.[1] In Unix time every day contains exactly 86400 seconds, and each leap second uses the timestamp of a second that immediately precedes or follows it.[1]

In DuckDB, an instant is a count from the Unix epoch 1970-01-01 00:00:00+00:00 in seconds, milliseconds, microseconds or nanoseconds, depending on the data type.[6] DuckDB's epoch function returns seconds since the epoch as a DOUBLE, keeping sub-second precision, while epoch_ms, epoch_us and epoch_ns return BIGINT counts.[2]

Sources

  1. [1]Unix time - Wikipediasecondary source
  2. [2]Timestamp Functions – DuckDBdocumentation
  3. [3]Timestamp with Time Zone Functions – DuckDBdocumentation
  4. [4]Date systems in Excel | Microsoft Supportdocumentation
  5. [5]Year 2038 problem - Wikipediasecondary source
  6. [6]Timestamp Types – DuckDBdocumentation