Java: PostgreSQL: Spring Boot: could not execute native query with interval and limit

Question

I'm working on the migration from MySql to PostgreSQL. So, I'm actualizing the Spring Data queries in repositories. And I've faced with the problem with the following native query:

@Query(value = "UPDATE some_table SET some_field= :someValue, changed_at=now() WHERE (changed_at < now() - INTERVAL :timeout MINUTE) LIMIT :limit", nativeQuery = true)
int updateRecords(@Param("someValue") String someValue, @Param("timeout") int timeoutMinutes, @Param("limit") int limit);

It works fine with the MySql, but with the PostgeSQL the fllowing error occurs:

org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute statement; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not execute statement
...
Caused by: org.hibernate.exception.SQLGrammarException: could not execute statement
...
Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near "$2"

Answer 1

You can't pass the length of the interval in that way.

It's easier to use make_interval() instead:

changed_at < now() - make_interval(mins => :timeout)

Or, if JPA also chokes on the =>

changed_at < now() - (interval '1 minute' * :timeout)