DuckDB provides us with a couple of easy ways to perform additions and subtractions on dates. In particular, we can use the - operator to do the job, or the date_add() operator combined with the - operator.
Here are two options for subtracting days from a date in DuckDB.
Option 1: The - Operator
One way to subtract days from a date in DuckDB is with the - operator. This can be used in the same manner that we might use it for when subtracting 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))) |
+-----------------------------------------------------------------------------------+
| 2049-12-29 00:00:00 |
+-----------------------------------------------------------------------------------+
We can also use the plural form DAYS instead of DAY:
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))) |
+-----------------------------------------------------------------------------------+
| 2049-12-29 00:00:00 |
+-----------------------------------------------------------------------------------+
Either way, it produces the 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) |
+----------------------------------+
| 2049-12-29 |
+----------------------------------+
When we do this, the result is returned as a DATE value, instead of a TIMESTAMP value.
Speaking of TIMESTAMP values, we can also subtract from 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))) |
+-------------------------------------------------------------------------------------------------+
| 2049-12-29 12:35:40 |
+-------------------------------------------------------------------------------------------------+
We can also subtract 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)) |
+----------------------------------------------------------------------------------------+
| 2049-12-28 21:54:43 |
+----------------------------------------------------------------------------------------+
When we do this, we need to pass the interval as a string (it needs to be enclosed in single quotes).
If we need to subtract a larger unit than days (such as months or years), then we can use the applicable keyword:
SELECT DATE '2050-03-31' - INTERVAL 1 MONTH;Result:
+-------------------------------------------------------------------------------------+
| (CAST('2050-03-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 subtract a month from the last day of a month that has more days than the resulting month, it knows to adjust the date accordingly so that it’s the last day of the resulting month. We can see this in the above example; We started with the 31st March and ended with 28th February.
Option 2: The DATE_ADD() Function
Alternatively, we can use the date_add() function in conjunction 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)))) |
+---------------------------------------------------------------------------------------------+
| 2049-12-29 00:00:00 |
+---------------------------------------------------------------------------------------------+
We can alternatively move the minus sign to negate the number:
SELECT
date_add( DATE '2050-01-01', INTERVAL '-3' DAY) AS c1,
date_add( DATE '2050-01-01', INTERVAL '-3 DAY') AS c2;Result:
+---------------------+---------------------+
| c1 | c2 |
+---------------------+---------------------+
| 2049-12-29 00:00:00 | 2049-12-29 00:00:00 |
+---------------------+---------------------+
We can see that it works whether we use '-1' DAY or '-1 DAY'.
We can use DAYS instead of DAY, and we can simply provide the number to subtract, 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 |
+---------------------+---------------------+------------+
| 2049-12-29 00:00:00 | 2049-12-29 00:00:00 | 2049-12-29 |
+---------------------+---------------------+------------+
We can also subtract 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))) |
+--------------------------------------------------------------------------------------------------+
| 2049-12-28 21:54:43 |
+--------------------------------------------------------------------------------------------------+