A Quick Look at EPOCH_MS() in DuckDB

Posted on by Ian

In DuckDB, the epoch_ms() function serves a dual purpose. It converts timestamp values into Unix epoch time in milliseconds and also performs the reverse operation, transforming Unix epoch time values back into timestamps.

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

The function is similar to the epoch() function, which returns its result in seconds. However, the epoch() function only works in one direction; it converts a timestamp value to epoch time, but it doesn’t work the other way around like epoch_ms() can.

Syntax

The epoch_ms() function can be used in two ways.

First syntax:

epoch_ms(timestamp)

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

Second syntax:

epoch_ms(milliseconds)

Where milliseconds is the Unix epoch time in milliseconds that we want converted to a timestamp.

Here’s a more detailed depiction of the syntax:

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

So we can pass a variety of different date/time values, and each one results in the epoch time being returned as a BIGINT. That is, except for the last form, which returns a TIMESTAMP value.

Converting to Epoch

Below are examples using epoch_ms() to convert from a date/time value to their epoch equivalents:

Passing a DATE Value

SELECT epoch_ms(DATE '2030-01-01');

Output:

+--------------------------------------+
| epoch_ms(CAST('2030-01-01' AS DATE)) |
+--------------------------------------+
| 1893456000000                        |
+--------------------------------------+

Passing a TIMESTAMP Value

SELECT epoch_ms(TIMESTAMP '2030-01-01 12:00:00');

Output:

+----------------------------------------------------+
| epoch_ms(CAST('2030-01-01 12:00:00' AS TIMESTAMP)) |
+----------------------------------------------------+
| 1893499200000                                      |
+----------------------------------------------------+

Passing an INTERVAL Value

SELECT epoch_ms(INTERVAL '3 years 4 months 10 days 7 hours 5 minutes');

Output:

+--------------------------------------------------------------------------+
| epoch_ms(CAST('3 years 4 months 10 days 7 hours 5 minutes' AS INTERVAL)) |
+--------------------------------------------------------------------------+
| 104569500000                                                             |
+--------------------------------------------------------------------------+

Passing a TIME Value

SELECT epoch_ms(TIME '11:30:45');

Output:

+------------------------------------+
| epoch_ms(CAST('11:30:45' AS TIME)) |
+------------------------------------+
| 41445000                           |
+------------------------------------+

Passing a TIMETZ Value

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

SELECT epoch_ms(TIMETZ '07:51:34+08:30');

Output:

+---------------------------------------------------------+
| epoch_ms(CAST('07:51:34+08:30' AS TIME WITH TIME ZONE)) |
+---------------------------------------------------------+
| 28294000                                                |
+---------------------------------------------------------+

Passing a TIMESTAMPTZ Value

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

SELECT epoch_ms(TIMESTAMPTZ '2018-08-12 18:16:27+12:00');

Output:

+-------------------------------------------------------------------------+
| epoch_ms(CAST('2018-08-12 18:16:27+12:00' AS TIMESTAMP WITH TIME ZONE)) |
+-------------------------------------------------------------------------+
| 1534054587000                                                           |
+-------------------------------------------------------------------------+

Converting Back from Epoch Time

DuckDB also allows us to convert epoch time back to timestamps using the make_timestamp() function:

SELECT make_timestamp(1997981672574400) AS converted_time;

Result:

+--------------------------+
|      converted_time      |
+--------------------------+
| 2033-04-24 18:54:32.5744 |
+--------------------------+

There’s also a to_timestamp() function that converts epoch time to a TIMESTAMP WITH TIME ZONE value. Be aware that this function accepts its epoch time in seconds (i.e. not milliseconds):

SELECT to_timestamp(1872574400) AS converted_time;

Result:

+------------------------+
|     converted_time     |
+------------------------+
| 2029-05-04 17:33:20+10 |
+------------------------+

More epoch...() Functions

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

  • epoch(): Returns the total number of seconds since the epoch.
  • epoch_us(): Returns the total number of microseconds since the epoch.
  • epoch_ns(): Return the total number of nanoseconds since the epoch.

These essentially work the same as epoch_ms(), except that they don’t allow us to run it in reverse and get the timestamp from an epoch time.