One common operation you may find yourself doing occasionally in DuckDB is adding intervals to date and/or time values. DuckDB provides us with a couple of easy ways to do this.

Here are two ways to add days to a date in DuckDB.

Option 1: The + Operator

One way to add days to a date in DuckDB is with the + operator. This can be used in the same manner that we might use it for when adding two numbers.

Example:

SELECT DATE '2050-01-01' + INTERVAL 3 DAY;

Result:

+-----------------------------------------------------------------------------------+
| (CAST('2050-01-01' AS DATE) + to_days(CAST(trunc(CAST(3 AS DOUBLE)) AS INTEGER))) |
+-----------------------------------------------------------------------------------+
| 2050-01-04 00:00:00                                                               |
+-----------------------------------------------------------------------------------+

We can also use the plural form for the DAY keyword:

SELECT DATE '2050-01-01' + INTERVAL 3 DAYS;

Result:

+-----------------------------------------------------------------------------------+
| (CAST('2050-01-01' AS DATE) + to_days(CAST(trunc(CAST(3 AS DOUBLE)) AS INTEGER))) |
+-----------------------------------------------------------------------------------+
| 2050-01-04 00:00:00                                                               |
+-----------------------------------------------------------------------------------+

Same result.

It’s also possible to provide just the number, instead of the whole INTERVAL part:

SELECT DATE '2050-01-01' + 3;

Result:

+----------------------------------+
| (CAST('2050-01-01' AS DATE) + 3) |
+----------------------------------+
| 2050-01-04                       |
+----------------------------------+

When we do this, the result is returned as a DATE value, instead of a TIMESTAMP value.

We can also add to a TIMESTAMP value if needed:

SELECT TIMESTAMP '2050-01-01 12:35:40' + INTERVAL 3 DAY;

Result:

+-------------------------------------------------------------------------------------------------+
| (CAST('2050-01-01 12:35:40' AS TIMESTAMP) + to_days(CAST(trunc(CAST(3 AS DOUBLE)) AS INTEGER))) |
+-------------------------------------------------------------------------------------------------+
| 2050-01-04 12:35:40                                                                             |
+-------------------------------------------------------------------------------------------------+

We can also add more than just days:

SELECT DATE '2050-01-01' + INTERVAL '3 DAYS 2 HOURS 5 MINUTES 17 SECONDS';

Result:

+----------------------------------------------------------------------------------------+
| (CAST('2050-01-01' AS DATE) + CAST('3 DAYS 2 HOURS 5 MINUTES 17 SECONDS' AS INTERVAL)) |
+----------------------------------------------------------------------------------------+
| 2050-01-04 02:05:17                                                                    |
+----------------------------------------------------------------------------------------+

When we do this, we need to pass the interval as a string (i.e. enclosed in single quotes).

If we need to add a larger unit than days (such as months or years), then we can use the applicable keyword:

SELECT DATE '2050-01-31' + INTERVAL 1 MONTH;

Result:

+-------------------------------------------------------------------------------------+
| (CAST('2050-01-31' AS DATE) + to_months(CAST(trunc(CAST(1 AS DOUBLE)) AS INTEGER))) |
+-------------------------------------------------------------------------------------+
| 2050-02-28 00:00:00                                                                 |
+-------------------------------------------------------------------------------------+

DuckDB knows how many days are in each month, so when we use MONTH to add a month to the last day of a given month that has more days than the resulting month, it knows to adjust the date accordingly. So in this example, we started with the 31st day of January, and ended with the 28th February.

Option 2: The DATE_ADD() Function

Alternatively, we can use the date_add() function to do the same thing that we did with the + operator:

SELECT date_add( DATE '2050-01-01', INTERVAL 3 DAY);

Result:

+------------------------------------------------------------------------------------------+
| date_add(CAST('2050-01-01' AS DATE), to_days(CAST(trunc(CAST(3 AS DOUBLE)) AS INTEGER))) |
+------------------------------------------------------------------------------------------+
| 2050-01-04 00:00:00                                                                      |
+------------------------------------------------------------------------------------------+

As with the + operator, we can use DAYS instead of DAY, and we can simply provide the number to add, rather than the whole INTERVAL part:

SELECT 
    date_add( DATE '2050-01-01', INTERVAL 3 DAY) AS DAY,
    date_add( DATE '2050-01-01', INTERVAL 3 DAYS) AS DAYS,
    date_add( DATE '2050-01-01', 3) AS Integer;

Result:

+---------------------+---------------------+------------+
|         DAY         |        DAYS         |  Integer   |
+---------------------+---------------------+------------+
| 2050-01-04 00:00:00 | 2050-01-04 00:00:00 | 2050-01-04 |
+---------------------+---------------------+------------+

We can also add more than just days, just like with the + operator:

SELECT date_add( DATE '2050-01-01', INTERVAL '3 DAYS 2 HOURS 5 MINUTES 17 SECONDS');

Result:

+-----------------------------------------------------------------------------------------------+
| date_add(CAST('2050-01-01' AS DATE), CAST('3 DAYS 2 HOURS 5 MINUTES 17 SECONDS' AS INTERVAL)) |
+-----------------------------------------------------------------------------------------------+
| 2050-01-04 02:05:17                                                                           |
+-----------------------------------------------------------------------------------------------+