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:

<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!

What is ts? Why do you have brackets in your query ? { ... } — Rick, Mar 29 '10 at 17:14
Trying to use a "JDBC escape syntax" timestamp literal as documented in the following links. It looks like DataNucleus' suggestion is a much better solution. http://openjpa.apache.org/builds/latest/docs/manual/jpa_langref.html#jpa_langref_lit http://publib.boulder.ibm.com/infocenter/cscv/v10r1/topic/com.ibm.cloudscape.doc/rrefjdbc41784.html#rrefjdbc41784 — Lightbeard, Mar 29 '10 at 19:02
You should open a bug against OpenJPA to get the code... or doc fixed. — Rick, Mar 30 '10 at 00:35

score 14 · Accepted Answer · edited Jan 18 '13 at 14:41

14

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.

edited Jan 18 '13 at 14:41

Tomasz Nurkiewicz

334,321
69
703
674

answered Mar 29 '10 at 17:33

DataNucleus

15,497
3
32
37

4

This is valid JPQL for our JPA implementation documented here: http://openjpa.apache.org/builds/latest/docs/manual/jpa_langref.html#jpa_langref_lit using "JDBC escape syntax." You should consider having some sympathy for people trying get started with a new technology. – Lightbeard Mar 29 '10 at 18:57
2

Yes, you're right; I'd forgotten how specific to RDBMS JPQL is. Advise would still be to avoid such syntax since, as per the JPA spec, you are dependent on the JDBC driver supporting it, and the query is actually much cleaner with a parameter. – DataNucleus Mar 30 '10 at 09:59
Offer a citation. – Matthew Daumen Apr 30 '19 at 16:21

score 10 · Answer 2 · answered Apr 01 '10 at 02:38

The JDBC escape syntax may not be supported in the version of OpenJPA that you're using. The documentation for the latest 1.2.x release is here: http://openjpa.apache.org/builds/1.2.2/apache-openjpa-1.2.2/docs/manual/manual.html#jpa_langref_lit .

The documentation mentioned earlier refers to the docs for OpenJPA 2.0.0 (latest): http://openjpa.apache.org/builds/latest/docs/manual/jpa_langref.html#jpa_langref_lit

That said is there any reason why you want to inject a string into your JPQL? What about something like the following snippet?

Date now = new Date();
Date thirtyDaysAgo = new Date(now.getTime() - (30 * MS_IN_DAY));

Query q = em.createQuery("Select m from Message m " 
    + "where m.targetTime < :now and m.targetTime > :thirtyDays");
q.setParameter("now", now); 
q.setParameter("thirtyDays", thirtyDaysAgo);

List<Message> results = (List<Message>) q.getResultList();

I would prefer using the parameter "now" instead of the JPQL function "current_timestamp" as in DataNucleus suggestion, since the function returns the time on the Database server and new Date() returns the time on the Java container. If they're not the same machine, this could lead to errors if and when the clocks don't sync. — Aviad Ben Dov, Sep 14 '11 at 03:43

How to do a timestamp comparison with JPA query?

2 Answers2

Linked