Subtract an interval from a time value

Question

I want to get the time 1 hour before the current time in PostgreSQL. How can I do that?

Say, for example, currently the time is '17:37:06', then I want the result output as '16:37:06'

Solution 1

If you really only want the time, use current_time current_timestamp or now() will return a date as well.

select current_time - interval '1' hour

or to subtract one hour and 25 minutes:

select current_time - interval '1:25'

(note that you need to remove the hour keyword if you use an interval with hours and minutes)

If you do want the date to be part of the result, just replace current_time with current_timestamp.

Solution 2

Use the interval type to do arithmetic on date types

select now() - interval '1 hour';

It is possible to use fractions like 1 hour and a half

select now() - 1.5 * interval '1 hour';