Performing date/time arithmetic is a common operation when using SQL databases such as DuckDB. As with most RDBMSs, DuckDB makes these types of operations relatively straightforward for us.

Here are two options for adding one or more seconds to a date, timestamp, or time value.

Option 1: The + Operator

One option is with the + operator. When we use this operator, it’s like adding two numbers together, except we’re adding an interval to a date/time value.

Here’s an example of adding 30 seconds to 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:30:35 | 2040-01-01 04:30:35 | 2040-01-01 00:00:30 |
+----------+---------------------+---------------------+

When we add a time interval like seconds to 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:30:35 | 2040-01-01 04:30:35 | 2040-01-01 00:00:30 |
+----------+---------------------+---------------------+

We can also add more than just seconds:

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

This requires us 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 achieve the same result that we got with the + operator:

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

Result:

+----------+---------------------+---------------------+
|   time   |      timestamp      |        date         |
+----------+---------------------+---------------------+
| 04:30:35 | 2040-01-01 04:30:35 | 2040-01-01 00:00:30 |
+----------+---------------------+---------------------+

We can replace SECOND with SECONDS to get the same result:

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

Result:

+----------+---------------------+---------------------+
|   time   |      timestamp      |        date         |
+----------+---------------------+---------------------+
| 04:30:35 | 2040-01-01 04:30:35 | 2040-01-01 00:00:30 |
+----------+---------------------+---------------------+

We can also add more than just seconds, 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 |
+----------+---------------------+---------------------+