Date operations in HQL

Question

I want to get expired entities from table using HQL. Something like that:

select id, name from entity_table where date_creation + 10_minutes < current_time()

I cant get how to do that with HQL. I DONT want to make extra-queries, process date in java code an so on. This must be done on HQL level.

Can you please help me?

Solution 1

Depending on your database, this can be trivially simple. HQL supports built-in vendor-specific features and functions, it also supports the ability to extend the dialect by registering new functions if they're not already supported by HQL.

Let's say you're using SQLServer (or Sybase). SQLServer has a function called 'DATEADD' that can do what you like very easily. The format is:

DATEADD (datepart, number, date)

You can use this function directly in HQL by first registering the function in your own Hibernate Dialect. To do this, you just have to extend the Dialect you're currently using. This is a very simple process.

First, create your own dialect class (replace 'SQLServer2008Dialect' with your own DB vendor):

public class MySQLServerDialect extends SQLServer2008Dialect {

  public MySQLServerDialect() {
    registerFunction("addminutes", new VarArgsSQLFunction(TimestampType.INSTANCE, "dateadd(minute,", ",", ")"));
  }

}

Next, modify your hibernate configuration to use this new class:

<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
  "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
  "http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
  <session-factory>
    ...
    <property name="hibernate.dialect">com.mycompany.MySQLServerDialect</property>
    ...
</hibernate-configuration>

Now simply use the function:

select x from MyEntity x where addminutes(x.creationDate, 10) < current_time()

(This assumes your entity is called MyEntity and the creation_date field is mapped to a property called creationDate).

Solution 2

HQL does not support arithmetic with date and time, so it is not possible without extending HQL. Easiest path is probably to register database vendors SQL dialect function for such a calculation. Other possibility is to implement and register interceptor (method to override is onPrepareStatement), if syntax with plus and minus signs is preferred.

When solution does not have to be purely in HQL and time from the JVM host is enough easiest solution is to calculate date that is 10 minutes in past and give it as an argument. If database time is preferred, that can be achieved by querying it in separate query and then decrementing 10 minutes from it.