hql current date functions

HQL Current Date And Other HQL Date Functions

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

mysql create triggerGetting the current date and time is probably one of the most common thing to do when working with queries and dates. HQL offers several functions or HQL expressions to get the current date:

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

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 sysdate() and current_date() or 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_date() and current_time() will return the date and time from Europe.

Also, 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.

Just remember:

If you are using current_time() then additions and subtractions are made on the seconds level.

HQL And datediff() Functions

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:

  1. Use SQL with createNativeQuery() and this will let you use database specific functions in the query
  2. 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();

Important Request

web design axioms we want you
[Source: https://dribbble.com/shots/2225825-We-need-you-flat]
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.

John Negoita

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

1 Comment

  1. […] 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. […]

    Reply

Leave a Reply

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

Scroll to top