How do I compare a time difference with a number of seconds in Postgres?

Question

I want to create a condition for expiration in Postgres. I've got a vartiable last_sync::timestamp and a limit 86400 * 30 seconds. I tried this way:

NOW() - last_sync > 86400 * 30

But it gives an error: no operator for interval > integer.

I would like to make it work even if last_sunc is -infinity.

How can I do this comparison correctly?

Answer 1

Use intervals ==> Date/Time Functions and Operators

create table asd(
   name varchar(20),
   last_sync timestamp
)
;

insert into asd values( 'one', now() - interval '500' hour );
insert into asd values( 'two', now() - interval '1500' hour );


SELECT * 
from asd
where NOW() - last_sync > ( 86400 * 30 ) * INTERVAL '1' second

name |last_sync           |
-----|--------------------|
two  |2016-10-26 00:52:16 |

---

How to make it work if last_sync is -infinity? – Fomalhaut 5 mins ago

insert into asd values( 'one', now() - interval '500' hour );
insert into asd values( 'two', now() - interval '1500' hour );

insert into asd values( 'minus infinity', timestamp '-infinity' );
insert into asd values( 'plus infinity', timestamp 'infinity' );


SELECT * 
from asd
where last_sync > NOW() - ( 86400 * 30 ) * INTERVAL '1' second

name          |last_sync                |
--------------|-------------------------|
one           |2016-12-06 15:52:12      |
plus infinity |292278994-08-17 00:00:00 |