Hibernate HQL date and datetime

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

Hibernate HQL date and datetime

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:

  • current_date()
  • current_time()
  • current_timestamp()

So, all these can be used for getting the current date or time in a HQL query, the only difference being the precision. Read more about working with HQL current date.

In Oracle You Can Use HQL trunc() Date Function

Another function you can use for HQL date comparison queries is the HQL trunc() function. This function discards the hour part of the date.

Please note that this will only work when using Hibernate with Oracle and the OracleDialect. This works because this dialect maps the trunc() HQL function to the Oracle trunc() function.

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

John Negoita

View posts by John Negoita
I'm a Java programmer, been into programming since 1999 and having tons of fun with it.
[jetpack-related-posts]

4 Comments

  1. AJSeptember 8, 2014

    I would recommend taking a look at .setTimestamp(). It looks like it might offer the same solution.

    Reply
  2. John-DanielDecember 12, 2014

    Restrictions.between has the same ability. I couldn’t use Criteria and this feature, the truncating of hours, minutes, seconds, has been something I’ve been trying to resolve for over a day.

    THANK-YOU! 🙂

    Reply
    1. John NegoitaFebruary 16, 2015

      You’re welcome John-Daniel!

      Reply
  3. […] for further details on how to bind the parameters you can read HQL Date and Datetime Quick Tip […]

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Scroll to top