JPQL, deleting values with timestamp older than 1 day

Question

I'm trying to write a JPQL statement that deletes all the values in a table with timestamp greater than 1 day. Right now I have a working statement that deletes all of the values older than the current time but I need to modify it so that it's deleting ones older than 1 day.

How can I modify this statement:

Query q = em.createQuery("DELETE FROM Statustable t WHERE t.ts < CURRENT_TIMESTAMP");

I've been trying to search the JPA API for how to manipulate the Current_TIMESTAMP value so that I could do something like "(CURRENT_TIMESTAMP -1)" to indicate "older than 1 day". I know that syntax is wrong but it should help you see what I'm trying to do.

Answer 1

The date functions of JPA are very limited, but your problem can be solved easily by substracting 1 day to the current date in Java, and then use a parameterized query:

Date yesterday = DateUtils.addDays(new Date(), -1);
Query q = em.createQUery("DELETE FROM Statustable t WHERE t.ts < :yesterday");
q.setParameter("yesterday", yesterday);