In PostgreSQL, we can use the - operator to subtract one or more hours from a time value.

By “time” value, this could be an actual time value, a timestamp, or an interval. We can also subtract hours from a date value or a date and time combination.

Example

We can specify intervals when performing arithmetic against dates and times. So to subtract one or more hours, we can use hour or hours:

SELECT time '07:00' - interval '1 hour';

Result:

06:00:00

Plural

And in plural form:

SELECT time '07:00' - interval '2 hours';

Result:

05:00:00

Timestamps

And here it is with a timestamp value:

SELECT timestamp '2030-01-20 09:00' - interval '30 hours';

Result:

2030-01-19 03:00:00

Intervals

We can also subtract hours from an interval:

SELECT interval '5 hours' - interval '2 hours';

Result:

03:00:00

Dates

We can even subtract hours from a date value:

SELECT date '2030-01-20' - interval '8 hours';

Result:

2030-01-19 16:00:00

The result is a timestamp value.

Date & Time Values Combined

We can also add a date and time value together, and subtract hours from that:

SELECT date '2030-01-20' + time '03:00' - interval '2 hours';

Result:

2030-01-20 01:00:00

Specified in Minutes

We can alternatively subtract the equivalent number in minutes:

SELECT time '15:45' - interval '120 minutes';

Result:

13:45:00

Specified in Days

If the hours to be subtracted are in 24 hour increments, we can alternatively use days:

SELECT timestamp '2030-01-20 15:45' - interval '1 day';

Result:

2030-01-19 15:45:00

Negative Values

It’s possible to perform date arithmetic with negative values. If we use a negative value with the - operator, then the specified number of hours will be added to the input date/time. But if we use it with the + operator, then it will be subtracted from the input date/time.

Example:

SELECT time '03:00' + interval '-2 hours';

Result:

01:00:00