10

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?

Fomalhaut
  • 8,590
  • 8
  • 51
  • 95
  • You have a interval between timestamp on the left, you probably need to convert this in seconds – AxelH Dec 27 '16 at 10:38
  • So how can I do it? – Fomalhaut Dec 27 '16 at 10:39
  • Have you read [How do I convert an interval into a number of hours with postgres?](http://stackoverflow.com/questions/952493/how-do-i-convert-an-interval-into-a-number-of-hours-with-postgres) or [How to convert an interval like “1 day 01:30:00” into “25:30:00”?](http://stackoverflow.com/q/341384/4391450) – AxelH Dec 27 '16 at 10:40
  • I have. It looks complicated for my problem. Maybe there's a better way for expiration. – Fomalhaut Dec 27 '16 at 10:41
  • 1
    You need to convert this interval into a number of seconds, the second links clearly answer this. An other solution would be to substract `86400*30` to NOW() and compare it to `last_sync`. This will give you two timestamp to compare, this is easier (or add the value to last_sync ;) ) – AxelH Dec 27 '16 at 10:43
  • Thanks @AxelH. I would like to have a look at other suggestions too. – Fomalhaut Dec 27 '16 at 10:45

3 Answers3

15

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 |
krokodilko
  • 35,300
  • 7
  • 55
  • 79
2

You can convert 86400 * 30 to an interval of seconds:

interval '2592000 second'

Then you can compare:

select (now() - '2016-12-02') > interval '2592000 second';

This select returns:

False

You can convert integer to interval:

(86400 * 30) * interval '1 second'
McNets
  • 10,352
  • 3
  • 32
  • 61
-1

If you read this as a Mathematician

NOW() - last_sync > 86400 * 30

would be equals to

NOW() > 86400 * 30 + last_sync

With the first line, you have interval > integer with the second line, you have timestamp > timestamp. No problem of type with the last one.

I don't know PostgreSQL enought to write the solution but you have a good start with that solution. Using the correct function to add a value to a timestamp.

AxelH
  • 14,325
  • 2
  • 25
  • 55
  • Same problem: `no operator for integer + timestamp` – Fomalhaut Dec 27 '16 at 10:50
  • 1
    If you tried using the code I have written, this seems logic since this was just a pseudocode... but reading to this [link](https://www.tutorialspoint.com/postgresql/postgresql_date_time.htm) to see how to add correctly the values you want could help. You need to write something like `+ interval '30 days'`. Note that I have done this search and found this link in less that 2minutes. I have no knowledge in postgresql specification – AxelH Dec 27 '16 at 10:56