Updated 20 April 2024 to include the date_add() function.

SQL Server has its DATEADD() function that adds an interval to a date value. MySQL’s DATE_ADD() and ADDDATE() for does the same thing, as does MariaDB’s DATE_ADD() and ADDDATE(). SQLite has a DATE() function that also provides the option of adding an interval to a given date.

Prior to version 16, PostgreSQL didn’t have a DATEADD() or equivalent function. But with PostgreSQL 16 came with the introduction of the date_add() function, which allows us to add an interval to a timestamp with time zone.

We can also add and subtract values from dates with date/time operators such as + and -.

The date_add() Function

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

Here’s an example of this function in action:

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

The third argument is optional. We can also provide a negative value for the interval in order to subtract from the value.

PostgreSQL 16 also introduced the date_subtract() function to allow us to subtract an interval from a timestamp with time zone.

Using Operators for Date Arithmetic

Add five days to a date:

SELECT date '2027-05-20' + integer '5';

Result:

2027-05-25

Subtract five days from a date using a negative amount:

SELECT date '2027-05-20' + integer '-5';

Result:

2027-05-15

Subtract five days from a date using the minus sign (-):

SELECT date '2027-05-20' - integer '5';

Result:

2027-05-15

Add five weeks to a date, specified by days:

SELECT date '2027-05-20' + integer '35';

Result:

2027-06-24

Add five weeks to a date using interval:

SELECT date '2027-05-20' + interval '5 week';

Result:

2027-06-24 00:00:00

Add five months to a date using interval:

SELECT date '2027-05-20' + interval '5 month';

Result:

2027-10-20 00:00:00

Add five hours to a date:

SELECT date '2027-05-20' + interval '5 hour';

Result:

2027-05-20 05:00:00

Add five minutes to a date:

SELECT date '2027-05-20' + interval '5 minute';

Result:

2027-05-20 00:05:00

Add a time to a date:

SELECT date '2027-05-20' + time '05:45';

Result:

2027-05-20 05:45:00