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?

Mogador

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

Brigandine

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. – Mogador

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 – Mogador

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

Answer 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)"));
      }
    }

Longinus

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

Answer 3

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

Twiggy

Answer 4

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.

Mauchi

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

Juxtaposition

Answer 6

The Hibernate issue Fred Haslam has referenced has been solved. With Hibernate 6.0+ versions, you can add Temporal Unit operations to date fields as simple as this:

Select m From MyObject m Where m.dateField + 10 MONTH - 5 DAY < current_date

*current_date being a Hibernate constant for current date.

Related Hibernate Issue

Temporal Units

Jurist

Answer 7

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

Macgregor

Answer 8

Hibernate4.3 provides native functions to access Dates/Timestamps and to perform arithmatic operations on them

Here is the link to the documentation of what expressions can be used in HQL. Few of which i am mentioning: To access date/time:

current_date(), current_time(), and current_timestamp()

Arithmetic operations can be done by following. These functions take Time as input:

second(...), minute(...), hour(...), day(...), month(...), and year(...)

One can get absolute difference of in HQL by

current_timestamp() - dateInstance

where dateInstance can have definition

@Column(name = "updated_at")
@Temporal(TemporalType.TIMESTAMP)
private java.util.Date updatedAt;

The result is in 0.1 seconds. So for a absolute difference of 1 second, the above expression will give result as 10.

So a query would look like :

select t from Table t where (current_timestamp() - updatedAt)>600

Leakey

Answer 9

Postgres users...

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

with SQL usage like:

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

Wernerwernerite