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

Getting 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.

HQL sysdate()

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:

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();

HQL Date Format

Let’s say you want to format a date in “YYYY-mm-dd” format using HQL.

How do we do that?

We have 2 options:

Option 1 – Format date with database engine specific functions

HQL sits on top of a database. That database for sure will have some date format functions, so you can use those in HQL.

This is the easier option, but please note that this means your app will not be database agnostic anymore. So, you’ll have to implement some fail-safe mechanisms (maybe make different HQL date formatting code depending on the database in the back).

Here’s how to format date in HQL for some of the most popular database:

HQL Date format in MySQL / MariaDB:

select DATE_FORMAT(e.eventDate,'%Y-%M-%d') from Event e

HQL Date format in SQL Server:

select FORMAT(MYDATE,'YYYY-MM-DD')from Event e

HQL Date format in Oracle / PostgreSQL:

select TO_CHAR(MYDATE,'YYYY-MM-DD')from Event e

Option 2 – Extend the HQL dialect with the date formatting function needed

Create your own class that extends the dialect you are currently using:

class CustomMySQL5Dialect extends MySQL5Dialect {
    CustomMySQL5Dialect() {
        // Add a generic 'date_format' function that can be used across databases
        registerFunction("date_format", new SQLFunctionTemplate(StandardBasicTypes.DATE, "date_format(?1, ?2)"));
    }
}

Then change your datasource to use the custom dialect, for example by changing hibernate.cfg.xml:

<property name="hibernate.dialect">com.codingdude.dialect.CustomMySQL5Dialect</property>

Now you can format date in HQL using DATE_FORMAT(MYDATE,'%Y-%M-%d')

Important Request

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.