Examples of EPOCH_US() in DuckDB

by

DuckDB provides us with a bunch of epoch...() functions that enable us to get the Unix epoch time from a given date/time value. Different functions return their result using different units (for example seconds, milliseconds, etc). The epoch_us() function returns its result in microseconds.

Unix epoch time is typically expressed as the number of seconds that have elapsed since January 1, 1970 (UTC), but epoch_us() function returns the equivalent amount in microseconds.

Syntax

The syntax for the epoch_us() function goes like this:

epoch_us(timestamp)

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

Here’s a more detailed depiction of the syntax:

epoch_us(DATE) -> BIGINT
epoch_us(TIMESTAMP) -> BIGINT
epoch_us(INTERVAL) -> BIGINT
epoch_us(TIME) -> BIGINT
epoch_us(TIME WITH TIME ZONE) -> BIGINT
epoch_us(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_us() with different date/time types.

Passing a DATE Value

SELECT epoch_us(DATE '2045-12-18');

Output:

+--------------------------------------+
| epoch_us(CAST('2045-12-18' AS DATE)) |
+--------------------------------------+
| 2397168000000000                     |
+--------------------------------------+

Passing a TIMESTAMP Value

SELECT epoch_us(TIMESTAMP '2045-12-18 13:15:27');

Output:

+----------------------------------------------------+
| epoch_us(CAST('2045-12-18 13:15:27' AS TIMESTAMP)) |
+----------------------------------------------------+
| 2397215727000000                                   |
+----------------------------------------------------+

Passing an INTERVAL Value

SELECT epoch_us(INTERVAL '10 years 7 months 14 days 3 hours 20 minutes');

Output:

+----------------------------------------------------------------------------+
| epoch_us(CAST('10 years 7 months 14 days 3 hours 20 minutes' AS INTERVAL)) |
+----------------------------------------------------------------------------+
| 330405600000000                                                            |
+----------------------------------------------------------------------------+

Passing a TIME Value

SELECT epoch_us(TIME '13:15:27');

Output:

+------------------------------------+
| epoch_us(CAST('13:15:27' AS TIME)) |
+------------------------------------+
| 47727000000                        |
+------------------------------------+

Passing a TIMETZ Value

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

SELECT epoch_us(TIMETZ '07:51:34+12:30');

Output:

+---------------------------------------------------------+
| epoch_us(CAST('07:51:34+12:30' AS TIME WITH TIME ZONE)) |
+---------------------------------------------------------+
| 28294000000                                             |
+---------------------------------------------------------+

Passing a TIMESTAMPTZ Value

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

SELECT epoch_us(TIMESTAMPTZ '2034-12-18 18:16:27+12:00');

Output:

+-------------------------------------------------------------------------+
| epoch_us(CAST('2034-12-18 18:16:27+12:00' AS TIMESTAMP WITH TIME ZONE)) |
+-------------------------------------------------------------------------+
| 2050035387000000                                                        |
+-------------------------------------------------------------------------+

More epoch...() Functions

DuckDB provides some other functions that we can use when working with epoch times:

  • 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_ns(): Return the total number of nanoseconds since the epoch.