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