An Overview of DuckDB’s EPOCH() Function

Posted on by Ian

DuckDB’s epoch() function is a useful time conversion tool that allows us to transform timestamp values into Unix epoch time – the number of seconds that have elapsed since January 1, 1970 (UTC).

The function is particularly useful when working with time-series data and when we need to perform mathematical operations on timestamp values. By converting timestamps to integer representations, we can easily calculate time differences, group time-based data, or integrate with systems that use epoch time.

Syntax

The basic syntax goes like this:

epoch(timestamp)

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

Here’s a more detailed depiction of the syntax:

epoch(DATE) -> DOUBLE
epoch(TIMESTAMP) -> DOUBLE
epoch(INTERVAL) -> DOUBLE
epoch(TIME) -> DOUBLE
epoch(TIME WITH TIME ZONE) -> DOUBLE
epoch(TIMESTAMP WITH TIME ZONE) -> DOUBLE

So we can pass a variety of different date/time values, and each one results in the epoch time being returned as a DOUBLE.

Examples

Below are examples of the epoch() function using different date/time types.

Passing a DATE Value

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

Output:

+-----------------------------------+
| epoch(CAST('2030-01-01' AS DATE)) |
+-----------------------------------+
| 1893456000.0                      |
+-----------------------------------+

Passing a TIMESTAMP Value

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

Output:

+-------------------------------------------------+
| epoch(CAST('2030-01-01 12:00:00' AS TIMESTAMP)) |
+-------------------------------------------------+
| 1893499200.0                                    |
+-------------------------------------------------+

Passing an INTERVAL Value

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

Output:

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

Passing a TIME Value

SELECT epoch(TIME '12:00:00');

Output:

+---------------------------------+
| epoch(CAST('12:00:00' AS TIME)) |
+---------------------------------+
| 43200.0                         |
+---------------------------------+

Passing a TIMETZ Value

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

SELECT epoch(TIMETZ '12:00:00+05:30');

Output:

+------------------------------------------------------+
| epoch(CAST('12:00:00+05:30' AS TIME WITH TIME ZONE)) |
+------------------------------------------------------+
| 43200.0                                              |
+------------------------------------------------------+

Passing a TIMESTAMPTZ Value

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

SELECT epoch(TIMESTAMPTZ '2025-03-08 12:00:00+05:30');

Output:

+----------------------------------------------------------------------+
| epoch(CAST('2025-03-08 12:00:00+05:30' AS TIMESTAMP WITH TIME ZONE)) |
+----------------------------------------------------------------------+
| 1741415400.0                                                         |
+----------------------------------------------------------------------+

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 |
+--------------------------+

One thing to be aware of with this function is that it requires the epoch time to be provided in microseconds.

There’s also a to_timestamp() function that converts epoch time to a TIMESTAMP WITH TIME ZONE value. This function accepts its epoch time in seconds:

SELECT to_timestamp(1872574400) AS converted_time;

Result:

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

Similar Functions

In addition to the make_timestamp() and to_timestamp() functions that convert the epoch time back to a timestamp value, DuckDB also has additional functions for converting a date/time values to their epoch equivalents. In particular:

  • 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(): 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(), except that they allow us to get the epoch time in different units, such as milliseconds, microseconds, and nanoseconds.

Integration with Other Systems

The epoch time format is a universal standard used across many systems and programming languages. Using epoch() (and the other epoch...() functions) in DuckDB can simplify data exchange with external systems that also use Unix time, such as many IoT platforms, logging systems, and time-series databases.