When working with SQL databases, one operation we often find ourselves performing is date/time arithmetic, such as adding or subtracting an interval to/from a date/time value. Fortunately, most RDBMSs make such operations quite easy to achieve, and DuckDB is no exception.

Below are two methods we can use in order to subtract hours from a date, timestamp, or time value in DuckDB.

Option 1: The - Operator

One way to subtract hours from a date/time value in DuckDB is with the - operator. This works just like subtracting a number from another, except that in this case we’re subtracting an interval from a date/time value.

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

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

Result:

+----------+---------------------+---------------------+
|   time   |      timestamp      |        date         |
+----------+---------------------+---------------------+
| 02:30:05 | 2040-01-01 02:30:05 | 2039-12-31 22:00:00 |
+----------+---------------------+---------------------+

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

We can also use the plural form HOURS instead of HOUR:

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

Result:

+----------+---------------------+---------------------+
|   time   |      timestamp      |        date         |
+----------+---------------------+---------------------+
| 02:30:05 | 2040-01-01 02:30:05 | 2039-12-31 22:00:00 |
+----------+---------------------+---------------------+

We can specify other interval units, such as minutes and seconds:

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 |
+----------+---------------------+---------------------+

When we do this, we need to pass the interval as a string (i.e. enclosed in single quotes).

Option 2: The DATE_ADD() Function

Alternatively, we can use the date_add() function to do the same thing that we did with the - operator:

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

Result:

+----------+---------------------+---------------------+
|   time   |      timestamp      |        date         |
+----------+---------------------+---------------------+
| 02:30:05 | 2040-01-01 02:30:05 | 2039-12-31 22:00:00 |
+----------+---------------------+---------------------+

We can replace HOUR with HOURS to get the same result:

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

Result:

+----------+---------------------+---------------------+
|   time   |      timestamp      |        date         |
+----------+---------------------+---------------------+
| 02:30:05 | 2040-01-01 02:30:05 | 2039-12-31 22:00:00 |
+----------+---------------------+---------------------+

We can also subtract more than just hours, just like with the - operator:

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 |
+----------+---------------------+---------------------+