Sometimes we need to subtract a certain time interval from a date when working with DuckDB. We might want to subtract hours, minutes, days, months, or even years.
Below are two methods we can use to subtract one or more years from a date in DuckDB.
Option 1: The - Operator
The - operator allows us to subtract time intervals from a date in DuckDB. This includes subtracting one or more years from the date.
Example:
SELECT DATE '2045-01-01' - INTERVAL 5 YEAR;Result:
+------------------------------------------------------------------------------------+
| (CAST('2045-01-01' AS DATE) - to_years(CAST(trunc(CAST(5 AS DOUBLE)) AS INTEGER))) |
+------------------------------------------------------------------------------------+
| 2040-01-01 00:00:00 |
+------------------------------------------------------------------------------------+
We can also use the plural form for the YEAR keyword (so that it becomes YEARS):
SELECT DATE '2045-01-01' - INTERVAL 5 YEARS;Result:
+------------------------------------------------------------------------------------+
| (CAST('2045-01-01' AS DATE) - to_years(CAST(trunc(CAST(5 AS DOUBLE)) AS INTEGER))) |
+------------------------------------------------------------------------------------+
| 2040-01-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 5 YEARS;Result:
+--------------------------------------------------------------------------------------------------+
| (CAST('2045-01-01 15:27:05' AS TIMESTAMP) - to_years(CAST(trunc(CAST(5 AS DOUBLE)) AS INTEGER))) |
+--------------------------------------------------------------------------------------------------+
| 2040-01-01 15:27:05 |
+--------------------------------------------------------------------------------------------------+
We can subtract multiple units at once:
SELECT DATE '2045-01-01' - INTERVAL '5 YEARS 6 MONTHS 5 DAYS 8 HOURS 3 MINUTES 43 SECONDS';Result:
+---------------------------------------------------------------------------------------------------------+
| (CAST('2045-01-01' AS DATE) - CAST('5 YEARS 6 MONTHS 5 DAYS 8 HOURS 3 MINUTES 43 SECONDS' AS INTERVAL)) |
+---------------------------------------------------------------------------------------------------------+
| 2039-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 year, so it works with leap years:
SELECT
DATE '2045-01-01' - INTERVAL 1 YEAR AS year,
DATE '2045-01-01' - INTERVAL 365 DAYS AS days;Result:
+---------------------+---------------------+
| year | days |
+---------------------+---------------------+
| 2044-01-01 00:00:00 | 2044-01-02 00:00:00 |
+---------------------+---------------------+
In this example we can see that providing 365 DAYS didn’t quite work, because 2044 is a leap year (which means that it has 366 days). On the other hand, using YEARS worked perfectly, because it took into account the leap year.
All of the above examples are returned as a TIMESTAMP, but we can cast it to a DATE value if required:
SELECT cast(DATE '2045-01-01' - INTERVAL 5 YEARS AS DATE);Result:
+--------------------------------------------------------------------------------------------------+
| CAST((CAST('2045-01-01' AS DATE) - to_years(CAST(trunc(CAST(5 AS DOUBLE)) AS INTEGER))) AS DATE) |
+--------------------------------------------------------------------------------------------------+
| 2040-01-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 5 YEAR );Result:
+----------------------------------------------------------------------------------------------+
| date_add(CAST('2045-01-01' AS DATE), -(to_years(CAST(trunc(CAST(5 AS DOUBLE)) AS INTEGER)))) |
+----------------------------------------------------------------------------------------------+
| 2040-01-01 00:00:00 |
+----------------------------------------------------------------------------------------------+
We can alternatively move the minus sign to negate the number:
SELECT
date_add( DATE '2045-01-01', INTERVAL '-5' YEAR ) AS c1,
date_add( DATE '2045-01-01', INTERVAL '-5 YEAR' ) AS c2;Result:
+---------------------+---------------------+
| c1 | c2 |
+---------------------+---------------------+
| 2040-01-01 00:00:00 | 2040-01-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 YEARS instead of YEAR:
SELECT date_add( DATE '2045-01-01', - INTERVAL 5 YEARS );Result:
+----------------------------------------------------------------------------------------------+
| date_add(CAST('2045-01-01' AS DATE), -(to_years(CAST(trunc(CAST(5 AS DOUBLE)) AS INTEGER)))) |
+----------------------------------------------------------------------------------------------+
| 2040-01-01 00:00:00 |
+----------------------------------------------------------------------------------------------+
We can specify multiple time units at the same time, just like with the - operator:
SELECT date_add( DATE '2045-01-01', - INTERVAL '5 YEARS 6 MONTHS 5 DAYS 8 HOURS 3 MINUTES 43 SECONDS') AS result;Result:
+---------------------+
| result |
+---------------------+
| 2039-06-25 15:56:17 |
+---------------------+
We can also cast the result as a DATE:
SELECT cast(date_add( DATE '2045-01-01', - INTERVAL 5 YEARS ) AS DATE) AS result;Result:
+------------+
| result |
+------------+
| 2040-01-01 |
+------------+
This time the result is returned as a DATE value rather than a TIMESTAMP.