Substract years from current_date in JPQL

Question

I want to delete the rows which are older than 2 years. In SQL I do it like this:

DELETE FROM table WHERE creation_date < (current_date - interval '2 year');

Now I want to do the same for my JPA Repository in Java with the JPQL. In JPQL I get an error because interval is not known and the "-"(minus) is not correct.

JPQL Query

@Query("DELETE FROM table t WHERE t.creation_date <  (current_date - interval '2 year')")

I would appreciate any suggestion.

Why just not use native query? AFAIK JPQL does not give you that option. https://stackoverflow.com/questions/48463143/spring-data-jpa-query-with-the-date-minus-2-days-not-working — Marcin Rzepecki, Oct 28 '20 at 17:18

score 2 · Answer 1 · answered Oct 28 '20 at 20:57

You can calculate the dateTime in the application, assuming you are using LocalDateTime for creation_date

LocalDateTime dateTime = LocalDateTime.now().minus(Period.ofYears(2));

and pass the parameter in JPQL

@Query("DELETE FROM table t WHERE t.creation_date < :dateTime")
void deleteByDateTime(LocalDateTime dateTime)

Substract years from current_date in JPQL

1 Answers1