Date_sub ok with mysql, ko with postgresql

Question

This query, which works with mySQL, does not work with Postgresql:

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

Error:

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

Any ideas?

+4

Bertaud May 01, '13 at 14:16

3 answers

For the interval literal, single quotes are needed:

 INTERVAL '30' MINUTE

And you can use the usual "arithmetic":

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

0

a_horse_with_no_name May 01, '13 at 14:21

Try using something like:

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

0

Igor Romanchenko May 01, '13 at 14:21

Joachim Isaksson · Accepted Answer · 2013-05-01T14:24:42+0000

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

You can (for example) use:

 NOW() - '30 MINUTES'::INTERVAL

... or...

 NOW() - INTERVAL '30' MINUTE

... or...

 NOW() - INTERVAL '30 MINUTES'

as a replacement.