How EPOCH_NS() Works in DuckDB

Posted on by Ian

DuckDB provides an epoch_ns() function that returns the Unix epoch time from a given date/time value in nanoseconds.

Unix epoch time is typically expressed as the number of seconds that have elapsed since January 1, 1970 (UTC), but it can also be expressed in other units, such as milliseconds, microseconds, and nanoseconds. DuckDB has specific functions for each of these units, with epoch_ns() being the function that returns it in nanoseconds.

Syntax

The syntax goes like this:

epoch_ns(timestamp)

Where timestamp is the timestamp that we want converted to the Unix epoch.

Here’s a more detailed depiction of the syntax:

epoch_ns(DATE) -> BIGINT
epoch_ns(TIMESTAMP) -> BIGINT
epoch_ns(INTERVAL) -> BIGINT
epoch_ns(TIME) -> BIGINT
epoch_ns(TIME WITH TIME ZONE) -> BIGINT
epoch_ns(TIMESTAMP WITH TIME ZONE) -> BIGINT

This shows us that we can pass a variety of different date/time values, and each one results in the epoch time being returned as a BIGINT.

Examples

Here are some examples that demonstrate epoch_ns() with different date/time types.

Passing a DATE Value

SELECT epoch_ns(DATE '1985-05-11');

Output:

+--------------------------------------+
| epoch_ns(CAST('1985-05-11' AS DATE)) |
+--------------------------------------+
| 484617600000000000                   |
+--------------------------------------+

Passing a TIMESTAMP Value

SELECT epoch_ns(TIMESTAMP '1985-11-19 13:15:27');

Output:

+----------------------------------------------------+
| epoch_ns(CAST('1985-11-19 13:15:27' AS TIMESTAMP)) |
+----------------------------------------------------+
| 501254127000000000                                 |
+----------------------------------------------------+

Passing an INTERVAL Value

SELECT epoch_ns(INTERVAL '3 years 4 months 17 days 2 hours 15 minutes');

Output:

+---------------------------------------------------------------------------+
| epoch_ns(CAST('3 years 4 months 17 days 2 hours 15 minutes' AS INTERVAL)) |
+---------------------------------------------------------------------------+
| 105156900000000000                                                        |
+---------------------------------------------------------------------------+

Passing a TIME Value

SELECT epoch_ns(TIME '08:45:12');

Output:

+------------------------------------+
| epoch_ns(CAST('08:45:12' AS TIME)) |
+------------------------------------+
| 31512000000000                     |
+------------------------------------+

Passing a TIMETZ Value

We can pass TIME WITH TIME ZONE values, also known as TIMETZ values:

SELECT epoch_ns(TIMETZ '17:20:15+08:00');

Output:

+---------------------------------------------------------+
| epoch_ns(CAST('17:20:15+08:00' AS TIME WITH TIME ZONE)) |
+---------------------------------------------------------+
| 62415000000000                                          |
+---------------------------------------------------------+

Passing a TIMESTAMPTZ Value

We can pass TIMESTAMP WITH TIME ZONE values, also known as TIMESTAMPTZ values:

SELECT epoch_ns(TIMESTAMPTZ '1987-10-05 16:15:21+11:00');

Output:

+-------------------------------------------------------------------------+
| epoch_ns(CAST('1987-10-05 16:15:21+11:00' AS TIMESTAMP WITH TIME ZONE)) |
+-------------------------------------------------------------------------+
| 560409321000000000                                                      |
+-------------------------------------------------------------------------+

Working with Dates Prior to January 1, 1970

If we pass a date that’s earlier than January 1, 1970 we’ll get a negative value:

SELECT epoch_ns(DATE '1927-01-01');

Output:

+--------------------------------------+
| epoch_ns(CAST('1927-01-01' AS DATE)) |
+--------------------------------------+
| -1356998400000000000                 |
+--------------------------------------+

More epoch...() Functions

DuckDB provides other functions that return the epoch time using different units, such as seconds, milliseconds, microseconds, etc:

  • epoch(): Returns the total number of seconds since the epoch.
  • epoch_ms(): Returns the total number of milliseconds since the epoch. This function can also be used the other way around; to convert integer milliseconds since the epoch to a timestamp.
  • epoch_us(): Return the total number of microseconds since the epoch.