Java: JPQL date function to add a time period to another date

Question

SELECT x FROM SomeClass
WHERE x.dateAtt BETWEEN CURRENT_DATE AND (CURRENT_DATE + 1 MONTH)

In the above JPQL statement, SomeClass has a memebr dateAttr, which is a java.util.Date and has a @Temporal(javax.persistence.TemporalType.DATE) annotation.

I need a way to do the (CURRENT_DATE + 1 MONTH) bit - it is obviously wrong in its current state - but cannot find the doc with the date function for JPQL.

Can anyone point me in the direction of a doc that documents JPQL date functions (and also how to do this particular query)?

Is this a query in some method you want? Or does this need to be in a trigger on the entity bean it self? IE: `@PrePersist` or `@PreUpdate`? — Shervin Asgari, May 18 '10 at 10:54
@Shervin : No, the query does not need to be performed in either `@PrePersist` or `@PreUpdate`. The query is a named query on a JPA Entity class (`SomeClass`). — bguiz, May 18 '10 at 11:07
@Shervin : Also, I know that I can use Java to compute the `Date` manipulations and then pass it into the query using a `:parameter`, however, I would much rather do it in the JPQL itself and avoid that unnecessary code. — bguiz, May 18 '10 at 11:09
I see, because thats what I would have suggested to do as a workaround. I am not sure these functions are a part of the JPA standards. Which database are you using? — Shervin Asgari, May 18 '10 at 11:18

Pascal Thivent · Answer 1 · 2010-05-18T14:17:24.937

22

Standard JPQL doesn't support such operations on dates. You will have to either use a native query or to do the computation on the Java side.

edited May 18 '10 at 14:17

answered May 18 '10 at 12:33

Pascal Thivent

562,542
136
1,062
1,124

19

Is this still true? – span Dec 18 '17 at 21:02

Shervin Asgari · Accepted Answer · 2010-05-18T20:27:22.603

20

If you have a date object that is + 1 month already you could do something like this:

public List findEmployees(Date endDate) {
  return entityManager.createQuery(
    "SELECT e from Employee e WHERE e.startDate BETWEEN ?1 AND ?2")
    .setParameter(1,new Date(), TemporalType.DATE)
    .setParameter(2,endDate, TemporalType.DATE).getResultList();
}

This however, requires that the dates be valid before hand.

UPDATE

If you always want the next month, you can use JodaTime which has a great and easy api. You could then modify your query like this:

//Get next month
DateTime dt = new DateTime();
entityManager.createQuery(
"SELECT e from Employee e WHERE e.startDate BETWEEN ?1 AND ?2")
.setParameter(1,new Date(), TemporalType.DATE)
.setParameter(2,dt.plusMonths(1).toDate(), TemporalType.DATE).getResultList();

edited May 18 '10 at 20:27

answered May 18 '10 at 11:24

Shervin Asgari

23,901
30
103
143

3

Did you try the JodaTime part? Can JPA deal with a `DateTime`? – Pascal Thivent May 18 '10 at 12:38
@Pascal: Good point. I forgot the toDate() method. This will return a `java.util.Date` – Shervin Asgari May 18 '10 at 20:27
1

Slight improvement: `.setParameter(1, dt.toDate(), TemporalType.DATE)` (one import less and consistency with the next line) – Sean Patrick Floyd Jan 21 '11 at 08:50
1

This doesn't really answer the question "JPQL date functions" as JPQL is a query specification by itself and the answers here are Java/JPA based solutions. Ways to get at the solution no doubt but ... again, I think the answer below "Standard JPQL does [not] support such operations on dates" is more correct. – Darrell Teague Mar 27 '13 at 19:05

score 5 · Answer 3 · answered May 18 '10 at 11:54

5

Or use commons-lang instead of jodatime:

entityManager.createQuery(
    "SELECT e from Employee e WHERE e.startDate BETWEEN ?1 AND ?2"
)
.setParameter(1,new Date(), TemporalType.DATE)
.setParameter(2,DateUtils.addMonths(new Date(), 1), TemporalType.DATE)
.getResultList();

But I know this is not what you are asking and I'm pretty sure it can't be done in JPQL alone, you will either have to pass a parameter or use a native named query

answered May 18 '10 at 11:54

Sean Patrick Floyd

292,901
67
465
588

`DateUtils`? Is that part of std JDK or is it a library like Joda time? – bguiz May 18 '10 at 13:41
it's in apache commons / lang, an open source library that is used internally by many frameworks: http://commons.apache.org/lang/ – Sean Patrick Floyd May 18 '10 at 14:08
2

Better to use Joda-Time because its a reference implementation of JSR-310 (https://jsr-310.dev.java.net/) – Shervin Asgari May 26 '10 at 14:45
2

true, but in most projects I have worked in, commons / lang was already in the classpath. And I don't think simple usage as seen above justifies the use of joda (if not used otherwise) – Sean Patrick Floyd May 26 '10 at 15:29

score -1 · Answer 4 · answered Jan 24 '16 at 06:26

Dears,

i'm using spring data, the entity save the createdDate as datetime, and in the repository like this:

@Query(value="SELECT t FROM MyEntity t WHERE t.createdDate Between ?1 and ?2")
public List<MyEntity> findAllBetweenDates(Calendar from, Calendar to);

so i can't use :

setParameter(1,new Date(), TemporalType.DATE

in the backend bean i use the following:

    //to set zero of hours,minutes,seconds and milliseconds
    fromCalendar.set(java.util.Calendar.HOUR, 0);
    fromCalendar.set(java.util.Calendar.MINUTE, 0);
    fromCalendar.set(java.util.Calendar.SECOND, 0);
    fromCalendar.set(java.util.Calendar.MILLISECOND, 0);

    toCalendar.set(java.util.Calendar.HOUR, 0);
    toCalendar.set(java.util.Calendar.MINUTE, 0);
    toCalendar.set(java.util.Calendar.SECOND, 0);
    toCalendar.set(java.util.Calendar.MILLISECOND, 0);
    // add 1 days and decrease 1 millisecond
    toCalendar.add(java.util.Calendar.DAY_OF_MONTH, 1);
    toCalendar.add(java.util.Calendar.MILLISECOND, -1);

    allEntities = myEntityRepository.findAllBetweenDates(fromCalendar, toCalendar);
}

and it's working fine.

Java: JPQL date function to add a time period to another date

4 Answers4

Linked