JPA query for day ignoring the time

Question

Let say you have a class with a java.util.Date field. Maybe a To do class or an Event planner class.

@Temporal(TemporalType.TIMESTAMP)
private Date startTime;

This will map to a datetime column in MySQL.

There are use cases when you want to know precisely when this Event occurs. "What is scheduled for Aug 11, 2011 at 8 PM?"

Sometimes you just want to know which events are planned for a specific date. "What is scheduled for August 11, 2011?"

If your JPAQL is:

SELECT blah blah etc. WHERE ti.startTime = :d1

and the parameter is a java.util.Date instance:

query.setParameter("d1", date, TemporalType.DATE);

your results will be restricted by the date but also the time.

This is such a common use case that I'm surprised that there is no simple way to do this even in JPA 2.0.

I'd rather not use a vendor specific hack nor play around with strings/substrings.

How have you solved this problem?

Answer 1

I know I'm a little late, but I've found a way to do this. I was using a jpql like this:

select s from S s where date(s.someDate) = :param

And I set the param with:

query.setParameter("param", param, TemporalType.DATE);

The way I've found to do this with Criteria Query was:

builder.equal(
 builder.function("date", Date.class, root.get(S_.someDate)), 
 param
);

At least, with MySql it works.

I hope it can help.

Answer 2

select ... where ti.startTime >= :theDay and ti.startTime < :theNextDay

is a relatively easy solution to implement, and works on any JPA implementation.

Hibernate also allows adding functions to a dialect, in order to generate custom SQL.