Compare date only (without time) in JPA2 (JPQL)

Question

Im trying to compare Calendars 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.

score 29 · Accepted Answer · answered May 08 '11 at 03:58

29

q.setParameter("calendar", c, TemporalType.DATE)

You can pass the TemporalType.DATE to setParameter method to truncate the date+time.

answered May 08 '11 at 03:58

Otávio Garcia

1,372
1
15
27

14

i 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? – Zavael Oct 19 '15 at 14:26
This is NOT working when we have @Temporal(TemporalType.TIMESTAMP) annotation on the column definition in the entity. – Buddhika Ariyaratne Jun 26 '21 at 20:41

score 8 · Answer 2 · answered May 04 '11 at 16:04

8

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

answered May 04 '11 at 16:04

JB Nizet

678,734
91
1,224
1,255

score 7 · Answer 3 · answered Jan 09 '19 at 14:26

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);`

Pierre · Answer 4 · 2019-05-07T14:23:02.173

5

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/

edited May 07 '19 at 14:23

answered May 07 '19 at 12:38

Pierre

51
1
6

score 0 · Answer 5 · answered Feb 19 '14 at 14:53

0

I had to use date_trunc on the where clause:

    TypedQuery<X> q = em.createQuery("select r from Record r where date_trunc('day',r).calendar= :calendar", X.class);
Calendar c = foo(); // setting fields and stuff
q.setParameter("calendar", c, TemporalType.DATE);

answered Feb 19 '14 at 14:53

Roger

29
6

Which provider are you using? – Jaumzera May 03 '16 at 16:32
1

i'm using hibernate – Roger Aug 24 '16 at 23:31

score 0 · Answer 6 · answered Feb 23 '23 at 19:31

0

In Hibernate 6 and above, you can use date_trunc(text, timestamp) to truncate the timestamp more precisely, for example:

date_trunc('hour', timestamp) to truncate the timestamp up to the hour (no minutes and no seconds).

answered Feb 23 '23 at 19:31

qetz

66
1
3

Compare date only (without time) in JPA2 (JPQL)

6 Answers6