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

By “time” value, this could be an actual time value, a timestamp, or an interval. We can also subtract seconds 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 seconds, we can use second or seconds:

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

Result:

06:59:59

Plural

And in plural form:

SELECT time '07:00' - interval '45 seconds';

Result:

06:59:15

Timestamps

And here it is with a timestamp value:

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

Result:

2030-01-20 08:59:30

Intervals

We can also subtract seconds from an interval:

SELECT interval '5 minutes' - interval '90 seconds';

Result:

00:03:30

Dates

We can even subtract seconds from a date value:

SELECT date '2030-01-20' - interval '12 seconds';

Result:

2030-01-19 23:59:48

The result is a timestamp value.

Date & Time Values Combined

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

SELECT date '2030-01-20' + time '01:00' - interval '18 seconds';

Result:

2030-01-20 00:59:42

Specified in Minutes

If the seconds to be subtracted are in 60 second increments, we can alternatively use minutes:

SELECT time '15:45' - interval '1 minute';

Result:

15:44: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 seconds 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 seconds';

Result:

02:59:58