How to perform date operations in hibernate

Question

I want to perform data time operations using hibernate HQL.

I want to add and subtract two dates as well as I want to subtract 1 year or 1 month from a particular date.

How is this possible using HQL in hibernate?

Answer 1

Postgres users...

registerFunction("dateadd", new SQLFunctionTemplate(StandardBasicTypes.DATE, "(?1 + INTERVAL ?2)"));

with SQL usage like:

now() < dateadd(:mydate, '-1 day')

Answer 2

See Performing Date/Time Math In HQL? for an example.

To use custom sql you must wrote an own hibernate dialect and register:

registerFunction("weekday", 
  new SQLFunctionTemplate(Hibernate.INTEGER, "to_char(?1,'D')") );

Answer 3

Disclaimer: I am a Java novice

I was able to use current_date() >= fromDate AND dateadd(day, -1, getdate()) <= toDate in an HQL statement against a Sybase db in Hibernate 3.5.3, without registering any functions.

Answer 4

Usage sample of approach with dialect for JPA + Hibernate 4.3.1 + MySQL 5

public class SampleMySQL5InnoDBDialect extends org.hibernate.dialect.MySQL5InnoDBDialect {

public SampleMySQL5InnoDBDialect() {
    super();
    registerFunction("date_sub_days", new SQLFunctionTemplate(StandardBasicTypes.DATE, "date_sub(?1, interval ?2 day)"));
}

then for your class with mapping annotation:

@NamedQuery(name = "SampleEntity.getSampleEntitiesForGapOverlapCalculations", query = "from SampleEntity as se where "
    + "((se.toDate between :startDate and :endDate) or (date_sub_days(se.toDate, se.duration) between :startDate and :endDate)) order by se.toDate asc, se.duration desc")

SampleEntity has toDate field of type java.sql.Date and duration integer field (duration in days) and we are calculating fromDate = toDate - duration and selecting all entities which have fromDate or toDate inside interval [startDate, endDate].

Answer 5

In Hibernate/MySQL (at least) You can convert to and from a Unix Timestamp. Since the unix timestamp is an integer you can add an integer number of seconds to it.

FROM_UNIXTIME(UNIX_TIMESTAMP(date)+ (allowedTimeWindow*86400)) as deadline

It has limitations but it's a lot easier than the approaches above.

Answer 6

This is an open issue in Hibernate. As of Hibernate 3.3 there is no standard way to handle date comparisons purely in HQL:

https://hibernate.atlassian.net/browse/HHH-2434

Hibernate 4.3 offers functions to access Date/Time in HQL which are:

current_timestamp() , current_date() , current_time()

Arithmetic operations can be managed by:

SECOND(...) , MINUTE(...) , HOUR(...) , DAY(...) , MONTH(...) , YEAR(...)