DuckDB provides us with the means to add and subtract intervals to/from date, timestamp, and time values. We can perform subtraction with the minus (-) operator or the date_add() function (along with the minus operator).

Below are examples of using each method to subtract minutes from date/time values.

Option 1: The - Operator

We can use the - operator to subtract one or more minutes from a date/time value. This is kind of like subtracting a number from another, except that we’re subtracting an interval from a date/time value.

Here’s an example of subtracting 15 minutes from a TIME value, a TIMESTAMP, and a DATE value:

SELECT 
    TIME '04:30:05' - INTERVAL 15 MINUTE AS time,
    TIMESTAMP '2040-01-01 04:30:05' - INTERVAL 15 MINUTE AS timestamp,
    DATE '2040-01-01' - INTERVAL 15 MINUTE AS date;

Result:

+----------+---------------------+---------------------+
|   time   |      timestamp      |        date         |
+----------+---------------------+---------------------+
| 04:15:05 | 2040-01-01 04:15:05 | 2039-12-31 23:45:00 |
+----------+---------------------+---------------------+

We can see that when we subtract minutes from a DATE value, the result is a TIMESTAMP value.

We can also use the plural form MINUTES instead of MINUTE:

SELECT 
    TIME '04:30:05' - INTERVAL 15 MINUTES AS time,
    TIMESTAMP '2040-01-01 04:30:05' - INTERVAL 15 MINUTES AS timestamp,
    DATE '2040-01-01' - INTERVAL 15 MINUTES AS date;

Result:

+----------+---------------------+---------------------+
|   time   |      timestamp      |        date         |
+----------+---------------------+---------------------+
| 04:15:05 | 2040-01-01 04:15:05 | 2039-12-31 23:45:00 |
+----------+---------------------+---------------------+

We can specify other interval units at the same time. When we do this, we need to pass the interval as a string (i.e. enclosed in single quotes):

SELECT 
    TIME '04:30:05' - INTERVAL '2 HOURS 5 MINUTES 20 SECONDS' AS time,
    TIMESTAMP '2040-01-01 04:30:05' - INTERVAL '2 HOURS 5 MINUTES 20 SECONDS' AS timestamp,
    DATE '2040-01-01' - INTERVAL '2 HOURS 5 MINUTES 20 SECONDS' AS date;

Result:

+----------+---------------------+---------------------+
|   time   |      timestamp      |        date         |
+----------+---------------------+---------------------+
| 02:24:45 | 2040-01-01 02:24:45 | 2039-12-31 21:54:40 |
+----------+---------------------+---------------------+

Option 2: The DATE_ADD() Function

We can use the date_add() function to do the same thing that we did with the - operator. Actually, when we use this function, we must also use the - operator in order subtract the value instead of adding it:

SELECT 
    date_add( TIME '04:30:05', - INTERVAL 15 MINUTE ) AS time,
    date_add( TIMESTAMP '2040-01-01 04:30:05', - INTERVAL 15 MINUTE ) AS timestamp,
    date_add( DATE '2040-01-01', - INTERVAL 15 MINUTE ) AS date;

Result:

+----------+---------------------+---------------------+
|   time   |      timestamp      |        date         |
+----------+---------------------+---------------------+
| 04:15:05 | 2040-01-01 04:15:05 | 2039-12-31 23:45:00 |
+----------+---------------------+---------------------+

As with the earlier example, we can replace MINUTE with MINUTES to get the same result:

SELECT 
    date_add( TIME '04:30:05', - INTERVAL 15 MINUTES ) AS time,
    date_add( TIMESTAMP '2040-01-01 04:30:05', - INTERVAL 15 MINUTES ) AS timestamp,
    date_add( DATE '2040-01-01', - INTERVAL 15 MINUTES ) AS date;

Result:

+----------+---------------------+---------------------+
|   time   |      timestamp      |        date         |
+----------+---------------------+---------------------+
| 04:15:05 | 2040-01-01 04:15:05 | 2039-12-31 23:45:00 |
+----------+---------------------+---------------------+

And of course, we can subtract more than just minutes:

SELECT 
    date_add( TIME '04:30:05', - INTERVAL '2 HOURS 5 MINUTES 25 SECONDS' ) AS time,
    date_add( TIMESTAMP '2040-01-01 04:30:05', - INTERVAL '2 HOURS 5 MINUTES 25 SECONDS' ) AS timestamp,
    date_add( DATE '2040-01-01', - INTERVAL '2 HOURS 5 MINUTES 25 SECONDS' ) AS date;

Result:

+----------+---------------------+---------------------+
|   time   |      timestamp      |        date         |
+----------+---------------------+---------------------+
| 02:24:40 | 2040-01-01 02:24:40 | 2039-12-31 21:54:35 |
+----------+---------------------+---------------------+