DuckDB provides us with a range of functions and operators that we can use to manipulate date, time, and timestamp values. One basic operation you might find yourself having to perform is date/time arithmetic, such as adding one or more hours to a date/time value.

Below are two methods we can use in order to add one or more hours to a date, timestamp, or time value.

Option 1: The + Operator

One way to add hours to a date/time value in DuckDB is with the + operator. This is basically like adding two numbers together, except we’re adding an interval to a date/time value.

Here’s an example of adding 3 hours to a TIME value, a TIMESTAMP, and a DATE value:

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

Result:

+----------+---------------------+---------------------+
|   time   |      timestamp      |        date         |
+----------+---------------------+---------------------+
| 07:30:05 | 2040-01-01 07:30:05 | 2040-01-01 03:00:00 |
+----------+---------------------+---------------------+

We can see that when we add hours to 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 3 HOURS AS time,
    TIMESTAMP '2040-01-01 04:30:05' + INTERVAL 3 HOURS AS timestamp,
    DATE '2040-01-01' + INTERVAL 3 HOURS AS date;

Result:

+----------+---------------------+---------------------+
|   time   |      timestamp      |        date         |
+----------+---------------------+---------------------+
| 07:30:05 | 2040-01-01 07:30:05 | 2040-01-01 03:00:00 |
+----------+---------------------+---------------------+

Same result.

We can also add more than just hours:

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

Result:

+----------+---------------------+---------------------+
|   time   |      timestamp      |        date         |
+----------+---------------------+---------------------+
| 07:35:25 | 2040-01-01 07:35:25 | 2040-01-01 03:05:20 |
+----------+---------------------+---------------------+

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 3 HOUR ) AS time,
    date_add( TIMESTAMP '2040-01-01 04:30:05', INTERVAL 3 HOUR ) AS timestamp,
    date_add( DATE '2040-01-01', INTERVAL 3 HOUR ) AS date;

Result:

+----------+---------------------+---------------------+
|   time   |      timestamp      |        date         |
+----------+---------------------+---------------------+
| 07:30:05 | 2040-01-01 07:30:05 | 2040-01-01 03:00:00 |
+----------+---------------------+---------------------+

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

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

Result:

+----------+---------------------+---------------------+
|   time   |      timestamp      |        date         |
+----------+---------------------+---------------------+
| 07:30:05 | 2040-01-01 07:30:05 | 2040-01-01 03:00:00 |
+----------+---------------------+---------------------+

We can also add more than just hours, just like with the + operator:

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

Result:

+----------+---------------------+---------------------+
|   time   |      timestamp      |        date         |
+----------+---------------------+---------------------+
| 07:35:30 | 2040-01-01 07:35:30 | 2040-01-01 03:05:25 |
+----------+---------------------+---------------------+