The make_timestamp() function in DuckDB is a handy tool for creating timestamp values from individual components. It allows us to construct timestamp values using two alternative approaches; by specifying the number of milliseconds from epoch, or by specifying the year, month, day, hour, minute, and second components separately.

Let’s take a look at the make_timestamp() function, along with some examples.

Syntax

As mentioned, we have two options when it comes to using this function:

Syntax 1: Component-Based Creation

make_timestamp(year, month, day, hour, minute, second)

Where:

• year: An integer representing the year
• month: An integer representing the month (1-12)
• day: An integer representing the day of the month
• hour: An integer representing the hour (0-23)
• minute: An integer representing the minute (0-59)
• second: A numeric value representing seconds, which can include fractional seconds

Syntax 2: Microseconds Since Epoch

make_timestamp(microseconds)

Where microseconds is an integer representing the number of microseconds since the Unix epoch (which is January 1, 1970, 00:00:00 UTC).

Component-Based Syntax

When using the component-based syntax, we provide individual datetime components:

SELECT make_timestamp(2025, 3, 10, 14, 30, 0) AS timestamp_from_components;

Result:

+---------------------------+
| timestamp_from_components |
+---------------------------+
| 2025-03-10 14:30:00       |
+---------------------------+

This syntax is useful when we have separate columns or values for year, month, day, etc., and need to combine them into a single timestamp.

We can use fractional seconds with this method:

SELECT make_timestamp(2025, 3, 10, 14, 30, 15.1234567) AS precise_timestamp;

Result:

+----------------------------+
|     precise_timestamp      |
+----------------------------+
| 2025-03-10 14:30:15.123457 |
+----------------------------+

Microseconds Since Epoch Syntax

The microsecond-based syntax converts an integer count of microseconds since the Unix epoch into a timestamp:

SELECT make_timestamp(1000000000) AS timestamp_from_micros;

Result:

+-----------------------+
| timestamp_from_micros |
+-----------------------+
| 1970-01-01 00:16:40   |
+-----------------------+

That’s a timestamp for 1 billion microseconds after epoch (Jan 1, 1970 + 1,000,000,000 μs). As you can imagine, it would take quite a large number to bring it up to anywhere near the current date:

SELECT make_timestamp(1750000000000000) AS timestamp_from_micros;

Result:

+-----------------------+
| timestamp_from_micros |
+-----------------------+
| 2025-06-15 15:06:40   |
+-----------------------+

This syntax is particularly useful when:

• Working with systems that store timestamps as microseconds
• Performing low-level timestamp arithmetic
• Converting between different timestamp representations

Usage Example

Suppose we have an event_log table that stores timestamps as microseconds since the Unix epoch. We could query such a table like this:

SELECT 
    event_id,
    event_micros,
    make_timestamp(event_micros) AS event_timestamp
FROM event_log;

Example output:

+----------+------------------+---------------------+
| event_id |   event_micros   |   event_timestamp   |
+----------+------------------+---------------------+
| 1        | 1672531200000000 | 2023-01-01 00:00:00 |
| 2        | 1672531260000000 | 2023-01-01 00:01:00 |
| 3        | 1672531320000000 | 2023-01-01 00:02:00 |
| 4        | 1672534800000000 | 2023-01-01 01:00:00 |
| 5        | 1678838400000000 | 2023-03-15 00:00:00 |
| 6        | 1678839300000000 | 2023-03-15 00:15:00 |
| 7        | 1678842900000000 | 2023-03-15 01:15:00 |
| 8        | 1688083200000000 | 2023-06-30 00:00:00 |
| 9        | 1688085000000000 | 2023-06-30 00:30:00 |
| 10       | 1688086800000000 | 2023-06-30 01:00:00 |
| 11       | 1706745600000000 | 2024-02-01 00:00:00 |
| 12       | 1711929600000000 | 2024-04-01 00:00:00 |
| 13       | 1717200000000000 | 2024-06-01 00:00:00 |
+----------+------------------+---------------------+

Here, the event_micros column is the raw data (the microseconds since the Unix epoch), and the event_timestamp is the same data after running it through the make_timestamp() function.

Passing an Invalid Date Component

When passing date components, we need to make sure that each component is valid, otherwise we’ll get an error. For example, passing a month of 13 results in an error:

SELECT make_timestamp(2025, 13, 10, 14, 30, 0) AS timestamp_from_components;

Result:

Conversion Error: Date out of range: 2025-13-10

Passing an Invalid Integer

When using the microseconds method, we need to make sure to pass a valid integer, otherwise an error occurs:

SELECT make_timestamp(1000000000.123) AS timestamp_from_micros;

Output:

Binder Error: No function matches the given name and argument types 'make_timestamp(DECIMAL(13,3))'. You might need to add explicit type casts.
	Candidate functions:
	make_timestamp(BIGINT, BIGINT, BIGINT, BIGINT, BIGINT, DOUBLE) -> TIMESTAMP
	make_timestamp(BIGINT) -> TIMESTAMP

LINE 1: SELECT make_timestamp(1000000000.123) AS times...
               ^

In this case, we could cast the number as an integer:

SELECT make_timestamp(cast(1000000000.123 as BIGINT)) AS timestamp_from_micros;

Output:

+-----------------------+
| timestamp_from_micros |
+-----------------------+
| 1970-01-01 00:16:40   |
+-----------------------+

Similar Functions

DuckDB has some similar functions that convert various epoch values to timestamps and vice-versa. In particular:

epoch_ms(ms)	Converts integer milliseconds since the epoch to a timestamp.
epoch_ms(timestamp)	Returns the total number of milliseconds since the epoch.
epoch_ns(timestamp)	Returns the total number of nanoseconds since the epoch.
epoch_us(timestamp)	Returns the total number of microseconds since the epoch.
epoch(timestamp)	Returns the total number of seconds since the epoch.