DuckDB provides us with an easy way to add one or more months to date values, as well as subtract from them. This applies to the various date related data types, such as DATE and TIMESTAMP.
Actually, DuckDB provides us with a choice; We can use the + operator or the date_add() function.
Option 1: The + Operator
The + operator allows us to add time intervals to a date in DuckDB. This includes adding one or more months to a date.
Example:
SELECT DATE '2045-01-01' + INTERVAL 6 MONTH;Result:
+-------------------------------------------------------------------------------------+
| (CAST('2045-01-01' AS DATE) + to_months(CAST(trunc(CAST(6 AS DOUBLE)) AS INTEGER))) |
+-------------------------------------------------------------------------------------+
| 2045-07-01 00:00:00 |
+-------------------------------------------------------------------------------------+
We can also use the plural form for the MONTH keyword (so that it becomes MONTHS):
SELECT DATE '2045-01-01' + INTERVAL 6 MONTHS;Result:
+-------------------------------------------------------------------------------------+
| (CAST('2045-01-01' AS DATE) + to_months(CAST(trunc(CAST(6 AS DOUBLE)) AS INTEGER))) |
+-------------------------------------------------------------------------------------+
| 2045-07-01 00:00:00 |
+-------------------------------------------------------------------------------------+
We can also add to a TIMESTAMP value if needed:
SELECT TIMESTAMP '2045-01-01 15:27:05' + INTERVAL 6 MONTHS;Result:
+---------------------------------------------------------------------------------------------------+
| (CAST('2045-01-01 15:27:05' AS TIMESTAMP) + to_months(CAST(trunc(CAST(6 AS DOUBLE)) AS INTEGER))) |
+---------------------------------------------------------------------------------------------------+
| 2045-07-01 15:27:05 |
+---------------------------------------------------------------------------------------------------+
We can also add more than just months:
SELECT DATE '2045-01-01' + INTERVAL '6 MONTHS 5 DAYS 8 HOURS 3 MINUTES 43 SECONDS';Result:
+-------------------------------------------------------------------------------------------------+
| (CAST('2045-01-01' AS DATE) + CAST('6 MONTHS 5 DAYS 8 HOURS 3 MINUTES 43 SECONDS' AS INTERVAL)) |
+-------------------------------------------------------------------------------------------------+
| 2045-07-06 08:03:43 |
+-------------------------------------------------------------------------------------------------+
When we do this, we need to pass the interval as a string (i.e. enclosed in single quotes).
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 following month, the result is the last day of the following month:
SELECT DATE '2045-01-31' + INTERVAL 1 MONTH;Result:
+-------------------------------------------------------------------------------------+
| (CAST('2045-01-31' AS DATE) + to_months(CAST(trunc(CAST(1 AS DOUBLE)) AS INTEGER))) |
+-------------------------------------------------------------------------------------+
| 2045-02-28 00:00:00 |
+-------------------------------------------------------------------------------------+
So in this example, we started with the 31st day of January, and ended with the 28th February. DuckDB knows not to return 31st of February, given that date doesn’t exist.
However, this concept doesn’t apply if the resulting month has more days than the current month:
SELECT DATE '2045-02-28' + INTERVAL 1 MONTH;Result:
+-------------------------------------------------------------------------------------+
| (CAST('2045-02-28' AS DATE) + to_months(CAST(trunc(CAST(1 AS DOUBLE)) AS INTEGER))) |
+-------------------------------------------------------------------------------------+
| 2045-03-28 00:00:00 |
+-------------------------------------------------------------------------------------+
In this case, we started at the 28th of February (its last day), and the result was the 28th of March – not the 31st of March. Even though March has 31 days, DuckDB didn’t just go ahead an assume that we wanted to move forward to the last day of March.
All of the above examples are returned as a TIMESTAMP. We can explicitly cast it to a DATE value if required:
SELECT cast(DATE '2045-01-01' + INTERVAL 6 MONTH AS DATE);Result:
+---------------------------------------------------------------------------------------------------+
| CAST((CAST('2045-01-01' AS DATE) + to_months(CAST(trunc(CAST(6 AS DOUBLE)) AS INTEGER))) AS DATE) |
+---------------------------------------------------------------------------------------------------+
| 2045-07-01 |
+---------------------------------------------------------------------------------------------------+
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 '2045-01-01', INTERVAL 6 MONTH );Result:
+--------------------------------------------------------------------------------------------+
| date_add(CAST('2045-01-01' AS DATE), to_months(CAST(trunc(CAST(6 AS DOUBLE)) AS INTEGER))) |
+--------------------------------------------------------------------------------------------+
| 2045-07-01 00:00:00 |
+--------------------------------------------------------------------------------------------+
As with the + operator, we can use MONTHS instead of MONTH:
SELECT date_add( DATE '2045-01-01', INTERVAL 6 MONTHS );Result:
+--------------------------------------------------------------------------------------------+
| date_add(CAST('2045-01-01' AS DATE), to_months(CAST(trunc(CAST(6 AS DOUBLE)) AS INTEGER))) |
+--------------------------------------------------------------------------------------------+
| 2045-07-01 00:00:00 |
+--------------------------------------------------------------------------------------------+
We can also add more than just days, just like with the + operator:
SELECT date_add( DATE '2045-01-01', INTERVAL '6 MONTHS 5 DAYS 8 HOURS 3 MINUTES 43 SECONDS');Result:
+--------------------------------------------------------------------------------------------------------+
| date_add(CAST('2045-01-01' AS DATE), CAST('6 MONTHS 5 DAYS 8 HOURS 3 MINUTES 43 SECONDS' AS INTERVAL)) |
+--------------------------------------------------------------------------------------------------------+
| 2045-07-06 08:03:43 |
+--------------------------------------------------------------------------------------------------------+
We can also cast the result as a DATE:
SELECT cast(date_add( DATE '2045-01-01', INTERVAL 6 MONTH ) AS DATE);Result:
+----------------------------------------------------------------------------------------------------------+
| CAST(date_add(CAST('2045-01-01' AS DATE), to_months(CAST(trunc(CAST(6 AS DOUBLE)) AS INTEGER))) AS DATE) |
+----------------------------------------------------------------------------------------------------------+
| 2045-07-01 |
+----------------------------------------------------------------------------------------------------------+