PostgreSQL 16 introduced the date_subtract() function that allows us to subtract an interval from 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_subtract ( timestamp with time zone, interval [, text ] )

Example

Here’s a basic example:

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

Result:

2024-04-17 00:00:00+07

This is similar to the following:

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

Result:

2024-04-17

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

Provide a Timestamp with Time Zone

Let’s pass a timestamp with time zone to the function:

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

Result:

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

Negative Intervals

Providing a negative interval adds that amount to the date instead of subtracting it:

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

Result:

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

Other Units

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

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

Result (using vertical output):

Year        | 2023-05-01 00:00:00+07
Month       | 2024-04-01 00:00:00+07
Day         | 2024-04-30 00:00:00+07
Hour        | 2024-04-30 23:00:00+07
Minute      | 2024-04-30 23:59:00+07
Second      | 2024-04-30 23:59:59+07
Microsecond | 2024-04-30 23:59:59.999999+07

Null Dates

Passing null for the date returns null:

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

Result:

NULL

Missing Arguments

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

SELECT date_subtract();

Result:

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