6 Functions for Working with the Unix Epoch in DuckDB

by

DuckDB offers a versatile set of functions to handle timestamps at various levels of precision. This article explores some of DuckDB’s functions that help us to convert between epoch representations and timestamps.

These specialized time conversion functions can be handy tools when working with temporal data, allowing seamless translation between human-readable timestamps and machine-optimized epoch representations at varying levels of precision.

What is Unix Epoch Time?

Before diving into the functions, let’s understand what epoch time is.

Unix epoch time (also known as epoch time, Unix time, Unix epoch, etc) represents the number of seconds (or milliseconds, microseconds, or nanoseconds) that have elapsed since January 1, 1970, at 00:00:00 UTC.

Unix epoch time representation is widely used in computing systems.

DuckDB’s Epoch Function Family

DuckDB provides some epoch...() functions that are designed specifically for working with epoch times, each with a different level of precision. There are also some other functions that deal with epoch values.

In particular:

  • 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(): Returns the total number of microseconds since the epoch.
  • epoch_ns(): Return the total number of nanoseconds since the epoch.
  • make_timestamp(): This function can accept a Unix epoch value as its argument in order to convert integer microseconds since the epoch to a timestamp.
  • to_timestamp(): Converts seconds since the epoch to a timestamp with time zone.

Converting Timestamps to Epoch Values

epoch()

The epoch() function converts a timestamp to the number of seconds since the Unix epoch:

SELECT epoch(current_timestamp);

Output:

+--------------------------+
| epoch(current_timestamp) |
+--------------------------+
| 1741642083.855           |
+--------------------------+

epoch_ms()

The epoch_ms() function converts a timestamp to the number of milliseconds since the Unix epoch:

SELECT epoch_ms(current_timestamp);

Result:

+-----------------------------+
| epoch_ms(current_timestamp) |
+-----------------------------+
| 1741642181783               |
+-----------------------------+

epoch_us()

The epoch_us() function converts a timestamp to the number of microseconds since the Unix epoch:

SELECT epoch_us(current_timestamp);

Output:

+-----------------------------+
| epoch_us(current_timestamp) |
+-----------------------------+
| 1741642220332000            |
+-----------------------------+

epoch_ns()

The epoch_ns() function converts a timestamp to the number of nanoseconds since the Unix epoch:

SELECT epoch_ns(current_timestamp);

Output:

+-----------------------------+
| epoch_ns(current_timestamp) |
+-----------------------------+
| 1741642284766000000         |
+-----------------------------+

Converting Epoch Values to Timestamps

Some of these functions enable us to work in reverse, converting an epoch value back to a timestamp.

epoch_ms() – From Milliseconds to Timestamp

SELECT epoch_ms(1615460745123);

Result:

+-------------------------+
| epoch_ms(1615460745123) |
+-------------------------+
| 2021-03-11 11:05:45.123 |
+-------------------------+

make_timestamp() – From Microseconds to Timestamp

SELECT make_timestamp(1615460745123456);

Result:

+----------------------------------+
| make_timestamp(1615460745123456) |
+----------------------------------+
| 2021-03-11 11:05:45.123456       |
+----------------------------------+

to_timestamp() – From Seconds to Timestamp with Time Zone

SELECT to_timestamp(1615460745.123456);

Result:

+---------------------------------+
| to_timestamp(1615460745.123456) |
+---------------------------------+
| 2021-03-11 21:05:45.123456+10   |
+---------------------------------+

Time Difference Calculation

Here’s an example of calculating the time difference between two events in seconds:

SELECT 
    timestamp '2024-10-15 14:30:00' - timestamp '2023-10-15 12:15:30' AS interval_difference,
    epoch(timestamp '2024-10-15 14:30:00') - epoch(timestamp '2023-10-15 12:15:30') AS seconds_difference;

Result:

+---------------------+--------------------+
| interval_difference | seconds_difference |
+---------------------+--------------------+
| 366 days 02:14:30   | 31630470.0         |
+---------------------+--------------------+

The left column returned the result as an INTERVAL value, whereas the right column returned the number of seconds as a DOUBLE value.