I need to replace this code from pgAdmin4:

DELETE from order_entity where (LOCALTIMESTAMP - local_date_time > '00:10:00')

to @Query Spring boot like:

@Query(" DELETE from OrderEntity o where (current_timestamp - o.localDateTime < '00:10:00')")

But i have and issue near where: '(', <expression>, FUNCTION or identifier expected, got '('

Help please.

CodePudding user response：

JPA according to the specification does not offer any method to subtract timestamps and for this reason the - operator is not recognized for that use that you have here with timestamps. So this is why you receive this error.

There are 3 type of solutions here:

• Make the calculation of current_timestamp - o.localDateTime in java and then pass the calculated value in the query. Then JPA would not have any issue with this query. For example where calc_diff < '00:10:00'.

• Use a native query where your DB vendor may support this action, with some specific function offered.

• 1. If you use, Eclipse Link as JPA implementation, take advantage of the Function method inside JPQL language with which you can call inside a JPA query specific functions that exist for your Database vendor.
  2. If you use Hibernate as JPA implementation, take advantage of the SQL method inside JQPL language with which you can call inside a JPA query specific functions that exist for your Database vendor.

  The above 2 methods for Eclipse Link/ Hibernate would allow you to have a JPQL query mixed with some native query for the part that is not supported from JPA, to subtract timestamps.