In PostgreSQL, we can use the - operator to subtract one or more minutes from a time value.
By “time” value, this could be an actual time value, a timestamp, or an interval. We can also subtract minutes 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 minutes, we can use minute or minutes:
SELECT time '07:00' - interval '1 minute';Result:
06:59:00
Plural
And in plural form:
SELECT time '07:00' - interval '45 minutes';Result:
06:15:00
Timestamps
And here it is with a timestamp value:
SELECT timestamp '2030-01-20 09:00' - interval '30 minutes';Result:
2030-01-20 08:30:00
Intervals
We can also subtract minutes from an interval:
SELECT interval '5 hours' - interval '90 minutes';Result:
03:30:00
Dates
We can even subtract minutes from a date value:
SELECT date '2030-01-20' - interval '12 minutes';Result:
2030-01-19 23:48:00
The result is a timestamp value.
Date & Time Values Combined
We can also add a date and time value together, and subtract minutes from that:
SELECT date '2030-01-20' + time '01:00' - interval '18 minutes';Result:
2030-01-20 00:42:00
Specified in Seconds
We can alternatively subtract the equivalent number in seconds:
SELECT time '15:45' - interval '120 seconds';Result:
15:43:00
Specified in Hours
If the minutes to be subtracted are in 60 minute increments, we can alternatively use hours:
SELECT time '15:45' - interval '1 hour';Result:
14: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 minutes 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 minutes';Result:
02:58:00