Understanding DATE_ADD() in DuckDB

DuckDB has a date_add() function, which allows us to add a specified time interval to a date or timestamp. This article looks at how the date_add() function works in DuckDB, including its syntax, usage, and examples.

What is the `date_add()` Function?

The date_add() function in DuckDB is used to add a specific interval to a date or timestamp. It can be particularly useful when we need to calculate future or past dates based on a given starting point. For example, we might want to find out what the date will be 10 days from now, or what the date was 3 months ago.

Syntax

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

date_add(date, interval)

date: This is the date or timestamp to which you want to add the interval.
interval: This is the interval you want to add to the date. For example, INTERVAL 3 MONTH. The function actually accepts other interval values, such as INTEGER, TIME, DOUBLE, etc.

The function is also quite flexible in that we can sometimes switch the arguments around and get the same result. We can also do things like add intervals to intervals, or time values to time values, etc.

Examples

Let’s look at some examples to understand how the date_add() function can be used in different scenarios.

Adding Days to a Date

Suppose you have a date 2025-10-01 and you want to find out what the date will be 15 days later. You can use the date_add() function as follows:

SELECT date_add(DATE '2025-10-01', INTERVAL 15 DAY);

Result:

+-------------------------------------------------------------------------------------------+
| date_add(CAST('2025-10-01' AS DATE), to_days(CAST(trunc(CAST(15 AS DOUBLE)) AS INTEGER))) |
+-------------------------------------------------------------------------------------------+
| 2025-10-16 00:00:00                                                                       |
+-------------------------------------------------------------------------------------------+

In this case, the result is returned as a TIMESTAMP.

To have it returned as a DATE value, we can pass an integer instead of the INTERVAL:

SELECT date_add(DATE '2025-10-01', 15);

Result:

+------------------------------------------+
| date_add(CAST('2025-10-01' AS DATE), 15) |
+------------------------------------------+
| 2025-10-16                               |
+------------------------------------------+

Subtracting Months from a Date

If you want to find out what the date was 3 months before 2025-10-01, you can use a negative value for the interval:

SELECT date_add(DATE '2025-10-01', - INTERVAL 3 MONTH);

Result:

+-----------------------------------------------------------------------------------------------+
| date_add(CAST('2025-10-01' AS DATE), -(to_months(CAST(trunc(CAST(3 AS DOUBLE)) AS INTEGER)))) |
+-----------------------------------------------------------------------------------------------+
| 2025-07-01 00:00:00                                                                           |
+-----------------------------------------------------------------------------------------------+

Adding Hours to a Timestamp

The date_add() function can also be used with timestamps. For example, if you have a timestamp 2025-10-01 12:00:00 and you want to add 6 hours to it, you can use the following query:

SELECT date_add(TIMESTAMP '2025-10-01 12:00:00', INTERVAL 6 HOUR);

Result:

+--------------------------------------------------------------------------------------------------------+
| date_add(CAST('2025-10-01 12:00:00' AS TIMESTAMP), to_hours(CAST(trunc(CAST(6 AS DOUBLE)) AS BIGINT))) |
+--------------------------------------------------------------------------------------------------------+
| 2025-10-01 18:00:00                                                                                    |
+--------------------------------------------------------------------------------------------------------+

Adding Multiple Interval Units

We can add multiple interval units in the same operation. For example, if we want to add 1 year, 2 months, and 3 days to the date 2023-10-01, we can do so as follows:

SELECT date_add(DATE '2023-10-01', INTERVAL '1 YEAR 2 MONTH 3 DAY');

Result:

+--------------------------------------------------------------------------------+
| date_add(CAST('2023-10-01' AS DATE), CAST('1 YEAR 2 MONTH 3 DAY' AS INTERVAL)) |
+--------------------------------------------------------------------------------+
| 2024-12-04 00:00:00                                                            |
+--------------------------------------------------------------------------------+

Note that when we do this, we need to pass the interval units as a string (they’re enclosed in single quotes).

Another way of adding multiple interval units is by chaining multiple date_add() functions:

SELECT date_add(date_add(date_add(DATE '2023-10-01', INTERVAL 1 YEAR), INTERVAL 2 MONTH), INTERVAL 3 DAY);

Result:

+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| date_add(date_add(date_add(CAST('2023-10-01' AS DATE), to_years(CAST(trunc(CAST(1 AS DOUBLE)) AS INTEGER))), to_months(CAST(trunc(CAST(2 AS DOUBLE)) AS INTEGER))), to_days(CAST(trunc(CAST(3 AS DOUBLE)) AS INTEGER))) |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 2024-12-04 00:00:00                                                                                                                                                                                                     |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Handling Differing Month Lengths

Given not all months have the same number of days, how does DuckDB calculate a “month”? For example, what happens if we add a month to 2025-01-31? Given February doesn’t have 31 days, what will DuckDB do? And what if it’s a leap year?

Let’s find out:

SELECT 
    date_add(DATE '2024-01-31', INTERVAL 1 MONTH) AS year2024,
    date_add(DATE '2025-01-31', INTERVAL 1 MONTH) AS year2025;

Result:

+---------------------+---------------------+
|      year2024       |      year2025       |
+---------------------+---------------------+
| 2024-02-29 00:00:00 | 2025-02-28 00:00:00 |
+---------------------+---------------------+

We can see that DuckDB was smart enough to know that February doesn’t contain 31 days, and so it adjusted the date accordingly. It knew that 31 was the last day of January, and so it returned the last day in February. Not only that, it also knew when to add a day for the leap year.

The same concept applies when adding years:

SELECT 
    date_add(DATE '2024-02-29', INTERVAL 12 MONTH) AS add12months,
    date_add(DATE '2024-02-29', INTERVAL 1 YEAR) AS add1year;

Result:

+---------------------+---------------------+
|     add12months     |      add1year       |
+---------------------+---------------------+
| 2025-02-28 00:00:00 | 2025-02-28 00:00:00 |
+---------------------+---------------------+

Here, we started with the 29th of February on a leap year. Adding 12 months had the same effect as adding 1 year, both of which resulted in the 28th of February being (correctly) returned, due to the following year not being a leap year.

Plural Intervals

As shown in some of the above examples, we can use plural interval units instead of singular, if we wish.

Here’s another example to demonstrate this:

SELECT 
    date_add(DATE '2025-10-01', INTERVAL 15 DAY) AS singular,
    date_add(DATE '2025-10-01', INTERVAL 15 DAYS) AS plural;

Result:

+---------------------+---------------------+
|      singular       |       plural        |
+---------------------+---------------------+
| 2025-10-16 00:00:00 | 2025-10-16 00:00:00 |
+---------------------+---------------------+

Decimal Unit Values

It’s possible to provide decimal values for the interval units, but these are rounded to integers.

Here’s an example to illustrate this:

SELECT 
    date_add(DATE '2025-01-01', INTERVAL '1.5' MICROSECONDS) AS microseconds,
    date_add(DATE '2025-10-01', INTERVAL '1.5' SECONDS) AS seconds,
    date_add(DATE '2025-10-01', INTERVAL '1.5' MINUTES) AS minutes,
    date_add(DATE '2025-10-01', INTERVAL '1.5' HOURS) AS hours,
    date_add(DATE '2025-01-01', INTERVAL '1.5' DAYS) AS days,
    date_add(DATE '2025-01-01', INTERVAL '1.5' MONTHS) AS months,
    date_add(DATE '2025-01-01', INTERVAL '1.5' YEARS) AS years;

Result:

+----------------------------+-----------------------+---------------------+---------------------+---------------------+---------------------+---------------------+
|        microseconds        |        seconds        |       minutes       |        hours        |        days         |       months        |        years        |
+----------------------------+-----------------------+---------------------+---------------------+---------------------+---------------------+---------------------+
| 2025-01-01 00:00:00.000001 | 2025-10-01 00:00:01.5 | 2025-10-01 00:01:00 | 2025-10-01 01:00:00 | 2025-01-02 00:00:00 | 2025-02-01 00:00:00 | 2026-01-01 00:00:00 |
+----------------------------+-----------------------+---------------------+---------------------+---------------------+---------------------+---------------------+

Therefore, to get the intended results, we should use a more granular interval. For example, instead of 1.5 YEARS, we can use 18 MONTHS.

Variations on the Syntax

As mentioned, the function is quite flexible in the values it accepts. For example we can switch it around so that the interval comes first, followed by the date. Or we can add a time value to the date. We can even pass two integers to be added, or even doubles.

Here are some examples:

SELECT 
    date_add(10, 10) AS c1,
    date_add(10.55, 1.12) AS c2,
    date_add(DATE '2025-10-01', 10) AS c3,
    date_add(DATE '2025-10-01', TIME '12:35:41')AS c4,
    date_add(INTERVAL '10 YEARS 2 MONTHS 3 DAYS', TIMESTAMP '2000-01-01 12:35:41') AS c5;

Result:

+----+-------+------------+---------------------+---------------------+
| c1 |  c2   |     c3     |         c4          |         c5          |
+----+-------+------------+---------------------+---------------------+
| 20 | 11.67 | 2025-10-11 | 2025-10-01 12:35:41 | 2010-03-04 12:35:41 |
+----+-------+------------+---------------------+---------------------+

What is the date_add() Function?

Syntax

Examples

Adding Days to a Date

Subtracting Months from a Date

Adding Hours to a Timestamp

Adding Multiple Interval Units

Handling Differing Month Lengths

Plural Intervals

Decimal Unit Values

Variations on the Syntax

What is the `date_add()` Function?