If you find yourself in the situation where you need to subtract a number of months from a date in DuckDB, here are two options for you.
Option 1: The - Operator
The - operator allows us to subtract time intervals from a date in DuckDB. This includes subtracting one or more months from the 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))) |
+-------------------------------------------------------------------------------------+
| 2044-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))) |
+-------------------------------------------------------------------------------------+
| 2044-07-01 00:00:00 |
+-------------------------------------------------------------------------------------+
It’s not limited to just DATE values. We can also subtract from a TIMESTAMP value for example:
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))) |
+---------------------------------------------------------------------------------------------------+
| 2044-07-01 15:27:05 |
+---------------------------------------------------------------------------------------------------+
We can also subtract 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)) |
+-------------------------------------------------------------------------------------------------+
| 2044-06-25 15:56:17 |
+-------------------------------------------------------------------------------------------------+
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 subtract a month from the last day of a month that has more days than the resulting month, the result is the last day of the resulting month:
SELECT DATE '2045-03-31' - INTERVAL 1 MONTH;Result:
+-------------------------------------------------------------------------------------+
| (CAST('2045-03-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 March, and ended with the 28th February. DuckDB knows not to return 31st of February, given that date doesn’t exist. This concept doesn’t apply if the resulting month has more days than the initial 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-01-28 00:00:00 |
+-------------------------------------------------------------------------------------+
In this case, the result is the 28th of January, even though January has 31 days.
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) |
+---------------------------------------------------------------------------------------------------+
| 2044-07-01 |
+---------------------------------------------------------------------------------------------------+
Option 2: The DATE_ADD() Function
DuckDB has a date_add() function that allows us to add intervals to dates. We can use this function to do the same thing that we did with the - operator. But when we use date_add() to subtract from a date, we must also use 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)))) |
+-----------------------------------------------------------------------------------------------+
| 2044-07-01 00:00:00 |
+-----------------------------------------------------------------------------------------------+
We can alternatively move the minus sign to negate the number:
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)))) |
+-----------------------------------------------------------------------------------------------+
| 2044-07-01 00:00:00 |
+-----------------------------------------------------------------------------------------------+
When we do this, we must use single quotes to prevent the minus sign from causing an error.
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)))) |
+-----------------------------------------------------------------------------------------------+
| 2044-07-01 00:00:00 |
+-----------------------------------------------------------------------------------------------+
We can also subtract 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))) |
+-----------------------------------------------------------------------------------------------------------+
| 2044-06-25 15:56:17 |
+-----------------------------------------------------------------------------------------------------------+
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) |
+-------------------------------------------------------------------------------------------------------------+
| 2044-07-01 |
+-------------------------------------------------------------------------------------------------------------+
This time the result is returned as a DATE value rather than a TIMESTAMP.