date_sub ok with mysql, ko with postgresql

Question

This query which works with mySQL doesn't work with Postgresql:

select ... from ... where (id = ... and ( h > date_sub(now(), INTERVAL 30 MINUTE)))  

The error is:

Query failed: ERREUR:  erreur de syntaxe sur ou près de « 30 »  

Any ideas ?

Answer 1

DATE_SUB is a MySQL function that does not exist in PostgreSQL.

You can (for example) either use;

NOW() - '30 MINUTES'::INTERVAL

...or...

NOW() - INTERVAL '30' MINUTE

...or...

NOW() - INTERVAL '30 MINUTES'

as a replacement.

An SQLfiddle with all 3 to test with.

Answer 2

An interval literal needs single quotes:

INTERVAL '30' MINUTE

And you can use regular "arithmetics":

and (h > current_timestamp - interval '30' minute)

Answer 3

Try using something like :

select ... 
from ... 
where id = ... 
  and  h > now() - INTERVAL '30 MINUTE'