Like most other DBMSs, DuckDB provides allows us to add and subtract intervals to/from date, timestamp, and time values. To perform a subtraction, we can use the minus (-) operator or the date_add() function (in conjunction with the minus operator).

Below are examples of using each of these options to subtract seconds from date/time values.

Option 1: The - Operator

We can use the - operator to subtract time intervals from a date/time value, including seconds. 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 30 seconds from a TIME value, a TIMESTAMP, and a DATE value:

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

Result:

+----------+---------------------+---------------------+
|   time   |      timestamp      |        date         |
+----------+---------------------+---------------------+
| 04:29:35 | 2040-01-01 04:29:35 | 2039-12-31 23:59:30 |
+----------+---------------------+---------------------+

We can see that when we perform this operation on a DATE value, the result is a TIMESTAMP value.

We can also use the plural form SECONDS instead of SECOND:

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

Result:

+----------+---------------------+---------------------+
|   time   |      timestamp      |        date         |
+----------+---------------------+---------------------+
| 04:29:35 | 2040-01-01 04:29:35 | 2039-12-31 23:59:30 |
+----------+---------------------+---------------------+

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 '15 MINUTES 20 SECONDS' AS time,
    TIMESTAMP '2040-01-01 04:30:05' - INTERVAL '15 MINUTES 20 SECONDS' AS timestamp,
    DATE '2040-01-01' - INTERVAL '15 MINUTES 20 SECONDS' AS date;

Result:

+----------+---------------------+---------------------+
|   time   |      timestamp      |        date         |
+----------+---------------------+---------------------+
| 04:14:45 | 2040-01-01 04:14:45 | 2039-12-31 23:44: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. But because this function adds instead of subtracts, we must include the - operator in order subtract the value instead of adding it:

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

Result:

+----------+---------------------+---------------------+
|   time   |      timestamp      |        date         |
+----------+---------------------+---------------------+
| 04:29:20 | 2040-01-01 04:29:20 | 2039-12-31 23:59:15 |
+----------+---------------------+---------------------+

And as you might expect, we can replace SECOND with SECONDS to get the same result:

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

Result:

+----------+---------------------+---------------------+
|   time   |      timestamp      |        date         |
+----------+---------------------+---------------------+
| 04:29:20 | 2040-01-01 04:29:20 | 2039-12-31 23:59:15 |
+----------+---------------------+---------------------+

And again, we can subtract more than just seconds:

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

Result:

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

We can use the same concept to add/subtract days, months, years, etc.