How to do a timestamp comparison with JPA query?

Question

We need to make sure only results within the last 30 days are returned for a JPQL query. An example follows: ``` Date now = new Date(); Timestamp thirtyDaysAgo = new Timestamp(now.getTime() - 86400000*30); Query query = em.createQuery( "SELECT msg FROM Message msg "+ "WHERE msg.targetTime < CURRENT_TIMESTAMP AND msg.targetTime > {ts, '"+thirtyDaysAgo+"'}"); List result = query.getResultList(); ``` Here is the error we receive: ``` org.apache.openjpa.persistence.ArgumentException: An error occurred while parsing the query filter 'SELECT msg FROM BroadcastMessage msg WHERE msg.targetTime < CURRENT_TIMESTAMP AND msg.targetTime > {ts, '2010-04-18 04:15:37.827'}'. Error message: org.apache.openjpa.kernel.jpql.TokenMgrError: Lexical error at line 1, column 217. Encountered: "{" (123), after : "" ``` Help!

Accepted Answer

So the query you input is not JPQL (which you could see by referring to the JPA spec). If you want to compare a field with a Date then you input the Date as a parameter to the query

```
msg.targetTime < CURRENT_TIMESTAMP AND msg.targetTime > :param
```

THIS IS NOT SQL.

How to do a timestamp comparison with JPA query?

Solution

Problem