This post will answer all you questions about how to use HQL current date and other HQL date functions. If it does not, drop me a question with details about your HQL date function related problem and I will try to find the solution for you.
Let’s dig in!
How To Get Current Date In Hibernate
current_date() returns the current date on the database client side – that is in the connection’s date time zone. The result only contains the year, month and day, not the time.
current_time() returns the date and time on the database client side.
current_timestamp() returns the timestamp on the database client side.
sysdate() returns the date and time on the database server.
It’s important to note a couple of differences between
current_time(). If the database server is in the US and the connection client is in Europe,
sysdate() will return the date and time in the US, while
current_time() will return the date and time from Europe.
sysdate() returns the time of it’s execution, while HQL date functions
current_X() return the time when the whole query finishes execution.
Why is this important? Let’s see an example:
select sysdate(),sysdate() from ImaginaryObject
will return something like:
2018-08-20 13:47:44, 2018-08-20 13:47:45
By the way, HQL date and time expressions and other HQL expressions are described in the HQL documentation though not too extensively.
Other HQL Date Functions
There are several other HQL date functions that might come in handy:
second(...)– returns the second from a date time parameter
minute(...)– returns the minute from a date time parameter
hour(...)– returns the hour from a date time parameter
day(...)– returns the day from a date time parameter
month(...)– returns the month from a date time parameter
year(...)– returns the year from a date time parameter
Let’s see a couple of HQL date function examples and assume that today is 2018-08-20 and the time 16:03:22 then:
select second(current_time()) ... //returns 22 select day(current_date()) ... //returns 20 select year(sysdate()) ... //returns 2018
HQL Compare Dates
HQL date comparison works as expected, using comparison operators. Let’s see an example on how to compare dates in HQL:
from Invoices inv where inv.dueDate <= current_date
This will select all invoices that are overdue or are due today. Please note that you can also use HQL date functions without parenthesis.
That was easy, right?
Well, there’s also the
between HQL operator which I’ve covered quite extensively in my post about Hibernate HQL Between Expression. So, give that a read if you want to use that.
When working with dates and times, sometimes you might want to ignore the time from the date. To do that you should use the
trunc() HQL function. That will truncate the time part of the date. If you want to know more about how to use HQL to compare date without time check out this HQL Date and Datetime Quicktip.
HQL Date Operations
You can also do various date and time operations with HQL. So, let’s see how to add days to a date in HQL.
This is important:
HQL date functions results will be evaluated depending on the context in which they are used.
That means that if you use HQL date functions with math operators together with numbers, the results will be evaluated as numbers. So this HQL query:
from Invoices inv where inv.dueDate <= current_date + 15
will return all invoices that are due in the next 15 days. Because
current_date returns the date without the time, it will be evaluated as a number down to the day. So, 2018-08-20 will be evaluated to 20,180,920.
HQL date subtraction works in a similar manner. HQL current date minus X number of days will return the date of X days ago.
If you are using
current_time() then additions and subtractions are made on the seconds level.
This is going to be short:
There’s no HQL function
datediff() for calculating the difference between two dates. In native SQL usually this function allows deciding the resolution for the difference (in days, hours, minutes, etc.)
Since there’s no such function in HQL you have two options:
- Use SQL with
createNativeQuery()and this will let you use database specific functions in the query
- Use common math with additions and subtractions like in the previous section. You only need to know that dates can be evaluated as numbers and a day has 24 hours, an hour 60 minutes… you get the picture.
How To Set Date Parameter In Hibernate Query
Let’s take an example
Query q = session.createQuery("from Invoices inv where inv.dueDate <= :dueDate"); q.setDate("dueDate", new java.util.Date());
And that’s how you set parameters in a HQL Hibernate query.
The important thing to note:
Hibernate cuts off time if you use
query.setDate(), so if you pass “2018-09-20 12:13:14” it becomes “2018-09-20 00:00:00”. Which in our case might not be exactly what you want. To go around that the solution is described in more detail in HQL Date and Datetime Quicktip:
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();
If you found any of the tips in this post useful, please drop me a short comment. Also, if you have questions or get stuck applying any of the techniques for working HQL current date and dates in general, I will try to answer all questions in the comments area.