21

Is there a way to put a date range condition referencing the current time in a JPA query (that is compatible across databases)?

I can do 

SELECT m FROM Mail m WHERE m.sentAt < :date

but I want to do it without having to bind a parameter (so that this can be configured as part of the named query repository and the date calculation logic does not have to enter the calling code).

So instead of :date I need "currentTime minus 3 minutes" as a hard-code literal.

Thilo
  • 257,207
  • 101
  • 511
  • 656
  • This related question seems to suggest that JPA itself does not do date arithmetics, but specific implementations might. I'd be okay with a Hibernate version. http://stackoverflow.com/questions/4105946/jpql-and-date-comparison-constraint-in-the-query?lq=1 – Thilo Aug 29 '13 at 08:09

3 Answers3

5

JPA supports the CURRENT_TIMESTAMP and CURRENT_TIME functions.

https://en.wikibooks.org/wiki/Java_Persistence/JPQL#Functions

The JPA spec does not have date functions, but some JPA providers such as EclipseLink do.

http://java-persistence-performance.blogspot.com/2012/05/jpql-vs-sql-have-both-with-eclipselink.html

JPA 2.1 also defines the FUNCTION operator to call database functions.

In EclipseLink I would try,

SELECT m FROM Mail m WHERE m.sentAt < SQL("(sysdate - interval '3' minute)")

or,

SELECT m FROM Mail m WHERE m.sentAt < SQL("(? - interval '3' minute)", CURRENT_TIMESTAMP)
James
  • 17,965
  • 11
  • 91
  • 146
5

If you are using Hibernate JPA implementation and an Oracle dialect, then you can use SYSDATE function which returns current date and time. If you add 1 to SYSDATE it will add one day.

Adding fractions is also supported:

  • sysdate + 1/24 will add an hour
  • sysdate + 1/(24*60) will add a minute
  • sysdate + 1/(24*60*60) will add a second

So instead of :date I need "currentTime minus 3 minutes" as a hard-code literal.

sysdate - 3/(24*60) will subtract 3 minutes from current date and time:

SELECT m FROM Mail m WHERE m.sentAt < sysdate - 3/(24*60)

Records older than 3 minutes will be returned and no parameter binding is needed.

AdrieanKhisbe
  • 3,899
  • 8
  • 37
  • 45
Sergej Panic
  • 854
  • 5
  • 8
  • getting 'The left expression is not an arithmetic expression' error if I use above expression – Rafael Ruiz Tabares Oct 11 '18 at 09:38
  • Are you using EclipseLink JPA implementation? I'm not sure if it works for EclipseLink. It definitively works for Hibernate JPA implementation on top of Oracle database. – Sergej Panic Oct 12 '18 at 09:12
0

I had a similar problem but I was able to solve it. Maybe this will help in the future:

This worked for me using springframework and a H2 database:

current_time >= w.lastRun + cast(w.checkIntervalInMin as double)/(24.0*60.0))

I found that explicit casting to double was required when using an integer column for the minutes, otherwise it would automatically round to 0 .

For hardcoded minutes, this should work:

SELECT m FROM Mail m WHERE m.sentAt < current_time - 3.0/(24.0*60.0)

xLorkin
  • 1