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?

Solution 1

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')") );

Solution 2

You need to create your own dialect. Something like the following:

public class MyDialect extends MySQLInnoDBDialect{
      public MyDialect() {
      super();
      registerFunction("date_add_interval", new SQLFunctionTemplate(Hibernate.DATE, "date_add(?1, INTERVAL ?2 ?3)"));
      }
    }

Solution 3

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.

Solution 4

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

Solution 5

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.

View more solutions

Comments

[amar4kintu]

Yes.. I saw that link but in Hibernate there is no provision given for adding or subtracting date properly. suppose if I want a date before a year using current_timestamp() of HQL. What should be done as there is no additional function given for that. Is there any better way? Thanks.

[amar4kintu]

Hello, on following link I found detailed description of creating custom hibernate dialect for sql server. It is explained in last answer of the link. Hope it will help someone. forums.hibernate.org/viewtopic.php?f=1&t=995494

[H2000]

Hi amar4kintu, if my answer has helped, can you please vote or mark the answer with the green checkmark?

[Jalal El-Shaer]

Great ... now how You use this in code ... a small sample if possible