PostgreSQL 16 introduced the date_add() function that allows us to add an interval to a timestamp with time zone.

It computes times of day and daylight-savings adjustments according to the time zone named by the third argument, or the current TimeZone setting if that is omitted.

Syntax

The syntax goes like this:

date_add ( timestamp with time zone, interval [, text ] )

Example

Here’s a basic example:

SELECT date_add(
    '2024-04-18'::date,
    '1 day'::interval
    );

Result:

2024-04-19 00:00:00+07

This is similar to the following:

SELECT date '2024-04-18' + integer '1';

Result:

2024-04-19

Although we can see that the date_add() function returns its result as a timestamp with time zone.

Provide a Timestamp with Time Zone

In this example I pass a timestamp with time zone to the function:

SELECT date_add(
    '2023-09-23 07:30:00-04'::timestamptz,
    '3 day'::interval,
    'America/New_York'
    );

Result:

2023-09-26 18:30:00+07

Negative Intervals

Providing a negative interval subtracts that amount from the date.

Example:

SELECT date_add(
    '2023-09-23 07:30:00-04'::timestamptz,
    '-3 day'::interval,
    'America/New_York'
    );

Result:

2023-09-20 18:30:00+07

Other Units

Here’s an example that adds an interval of 1 to various date and time units:

SELECT 
    date_add( '2024-05-01'::date, '1 year'::interval ) AS "Year",
    date_add( '2024-05-01'::date, '1 month'::interval ) AS "Month",
    date_add( '2024-05-01'::date, '1 day'::interval ) AS "Day",
    date_add( '2024-05-01'::date, '1 hour'::interval ) AS "Hour",
    date_add( '2024-05-01'::date, '1 minute'::interval ) AS "Minute",
    date_add( '2024-05-01'::date, '1 second'::interval ) AS "Second",
    date_add( '2024-05-01'::date, '1 microsecond'::interval ) AS "Microsecond";

Result (using vertical output):

Year        | 2025-05-01 00:00:00+07
Month       | 2024-06-01 00:00:00+07
Day         | 2024-05-02 00:00:00+07
Hour        | 2024-05-01 01:00:00+07
Minute      | 2024-05-01 00:01:00+07
Second      | 2024-05-01 00:00:01+07
Microsecond | 2024-05-01 00:00:00.000001+07

Null Dates

Passing null for the date returns null:

SELECT date_add( null, '1 year'::interval );

Result:

NULL

Missing Arguments

Calling date_add() with the wrong number of arguments, or without passing any arguments results in an error:

SELECT date_add();

Result:

ERROR:  function date_add() does not exist
LINE 1: SELECT date_add();
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.