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:
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.
I would recommend taking a look at .setTimestamp(). It looks like it might offer the same solution.
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! 🙂
You’re welcome John-Daniel!
[…] for further details on how to bind the parameters you can read HQL Date and Datetime Quick Tip […]
[…] HQL Date and Datetime Comparison Quick Tip […]
This article and your StackOverflow answer saved me a ton of headache, Thanks a lot!
🙂 glad it got you out of trouble. When I had my own problems with HQL date, I was so happy to get it solved that I decided to share my findings to help people just like you