Compare date only (without time) in JPA2 (JPQL)
Solution 1
q.setParameter("calendar", c, TemporalType.DATE)
You can pass the TemporalType.DATE to setParameter method to truncate the date+time.
Solution 2
There is no mention of DateTime functions allowing to do that in the spec of JPQL, but you could always cheat and do
select r from Record r where r.calendar >= :theDayAtZeroOClock and r.calendar < :theDayAfterAtZeroOClock
Solution 3
Mysql and H2 compatible comparison of dates ignoring time part:
`@Query("SELECT DISTINCT s " +
"FROM Session s " +
"JOIN s.movie m " +
"WHERE m.id = :movie AND CAST(s.time AS date) = CAST(:date AS date) " +
"ORDER BY s.time")
List<Session> getByMovieAndDate(@Param("movie") Long movie, @Param("date") LocalDateTime date);`
Solution 4
When using an Oracle database, you can use the trunc
function in your JPQL query, e.g.:
TypedQuery<X> q = em.createQuery("select r from Record r where trunc(r.calendar) = trunc(:calendar)", X.class);
See also https://cirovladimir.wordpress.com/2015/05/18/jpa-trunc-date-in-jpql-query-oracle/
Comments
-
atamanroman about 4 years
Im trying to compare
Calendar
s with JPA2. The query looks somewhat like that:TypedQuery<X> q = em.createQuery("select r from Record r where r.calendar= :calendar", X.class); Calendar c = foo(); // setting fields and stuff q.setParameter("calendar", c);
This, however, compares the date + time. I want to know if MM:DD:YYYY is equal and do not care about the time. Is there a nice way to do that in JPA2 or do I have to create a native query?
I tried setting HH:MM:SS:... to zero before saving it in the db but I don't know if this is very wise, regarding time zones and daylight saving and stuff.
-
Zavael over 8 yearsi think its not working when you have @Temporal(TemporalType.TIMESTAMP) annotation on the column definition in the entity. Do you know how to fix this case?
-
Jaumzera about 8 yearsWhich provider are you using?
-
Roger over 7 yearsi'm using hibernate
-
Buddhika Ariyaratne almost 3 yearsThis is NOT working when we have @Temporal(TemporalType.TIMESTAMP) annotation on the column definition in the entity.