How to do a timestamp comparison with JPA query?

jpa jpql openjpa
Lightbeard picture Lightbeard · Mar 29, 2010 · Viewed 54.7k times · Source

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:

<openjpa-1.2.3-SNAPSHOT-r422266:907835 nonfatal user error> 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!

Answer

DataNucleus picture DataNucleus · Mar 29, 2010

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.

Related questions

Adding IN clause List to a JPA Query

I have built a NamedQuery that looks like this: @NamedQuery(name = "EventLog.viewDatesInclude", query = "SELECT el FROM EventLog el WHERE el.timeMark >= :dateFrom AND " + "el.timeMark <= :dateTo AND " + "el.name IN (:inclList)") What I want to do is …

Read More
JPQL IN clause: Java-Arrays (or Lists, Sets...)?

I would like to load all objects that have a textual tag set to any of a small but arbitrary number of values from our database. The logical way to go about this in SQL would be to build an "…

Read More
IN-clause in HQL or Java Persistence Query Language

I have the following parametrised JPA, or Hibernate, query: SELECT entity FROM Entity entity WHERE name IN (?) I want to pass the parameter as an ArrayList<String>, is this possible? Hibernate current tells me, that java.lang.ClassCastException: …

Read More