HQL Date and Datetime Comparison Quick Tip

I’m a big fan of Hibernate and I often use in my projects HQL queries. In this post I will give you some quick tips regarding the use of date and datetime data types in HQL. In a previous post I was looking at the HQL date operator BETWEEN, but in the current post we will be looking at how to correctly bind date and datetime parameters in HQL queries.

The documentation for HQL was not really helpful for me in this situation.

The problem: HQL date vs datetime queries

When you need to do HQL date comparison you need to take into account if you want to compare dates at day level or at hour/second level.

Let’s see an example:

public class Event{

int id;

String name;

Date eventDate;

}

Mapped with Hibernate on a table with the following structure:

CREATE TABLE IF NOT EXISTS `event` (
`id` int(11) NOT NULL,
`name` varchar(255) NOT NULL,
`event_date` datetime NOT NULL
)

I tried to run a custom query to show me all events that happen today. So I’ve made an query that returns all events with the date and time between start of the day today (00:00 in the morning) and today end of the day (23:59:59) .The Java code with the HQL date query looked something like:

Date today = new Date();
Date todayMorning = DateUtils.truncate(today, Calendar.DATE);
Date todayEvening = DateUtils.addSeconds(DateUtils.addMinutes(DateUtils.addHours(todayMorning, 23), 59), 59);
getSession().createQuery(
"from Event e where e.eventDate between :start and :end"
)
.setDate("start",todayMorning)
.setDate("end",todayEvening)
list();//not working because setDate truncates the date to day level

This did not return the correct results.

So, why doesn’t this work?

Because “setDate” will truncate the HQL date value passed as parameter and ignore the hours, minutes, seconds. This is very important to note especially if you have an HQL query checking between dates from different days because using “setDate” will interpret the date interval as between midnight of the specified dates.

The solution – correct HQL date time parameter binding

In order for the HQL date query to return the correct results the following code worked:

Date today = new Date();
Date todayMorning = DateUtils.truncate(today, Calendar.DATE);
Date todayEvening = DateUtils.addSeconds(DateUtils.addMinutes(DateUtils.addHours(todayMorning, 23), 59), 59);
getSession().createQuery(
"from Event e where e.eventDate between :start and :end"
)
.setParameter("start",todayMorning)
.setParameter("end",todayEvening)
list();

The difference is that we use “setParameter” instead of “setDate” which causes the HQL date values to be interpreted as dates and times.

Working With The HQL Current Date

To directly use the current date in HQL there are 3 HQL functions:

Hope you found this short post useful and if you have any questions please do not hesitate to drop me a comment below.