Migrating hardcoded query from MYSQL to PostgreSQL

Question

Hi I have one "mysql" query that looks like this:

... WHERE (maintenanceLockTimestamp + "+ delay+" SECOND) < CURRENT_TIMESTAMP ) ) "

The thing is that, we need to move from MYSQL to PostgreSQL, but when executing the same query, I get:

Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near "SECONDS"

I checked here: https://www.postgresqltutorial.com/postgresql-date-functions/

and it looks like the SECOND function is not present in PostgreSQL, do you know any alternative?

thanks

Answer 1

In Postgres, you can use:

where maintenanceLockTimestamp + :delay * interval '1 second' < current_timestamp

Note the use of a parameter to avoid munging the query string with literal values.

In both databases, this is better written with the date/time arithmetic on the current_timestamp:

where maintenanceLockTimestamp < current_timestamp - ? second  -- MySQL
where maintenanceLockTimestamp < current_timestamp - :delay * interval '1 second'  -- Postgres

This allows the optimizer to use an index on maintenanceLockTimestamp for performance.