CODEWITHSUNDEEP
hibernatedatetimehql
amar4kintu
amar4kintu

Reputation: 841

How to perform date operations in hibernate

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?

Upvotes: 18

Views: 79028

Answers (9)

MrMikopi
MrMikopi

Reputation: 21

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

Upvotes: 2

Mark Bolusmjak
Mark Bolusmjak

Reputation: 24399

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

Upvotes: 10

Fred Haslam
Fred Haslam

Reputation: 9033

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

Upvotes: 5

Joseph Valerio
Joseph Valerio

Reputation: 113

Postgres users...

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

with SQL usage like: 

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

Upvotes: 0

H2000
H2000

Reputation: 919

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

Upvotes: 7

Nikhil Sahu
Nikhil Sahu

Reputation: 2621

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

Upvotes: 0

ydrozhdzhal
ydrozhdzhal

Reputation: 164

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

Upvotes: 0

Dean Mathieson
Dean Mathieson

Reputation: 51

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.

Upvotes: 5

foson
foson

Reputation: 10227

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.

Upvotes: 1

Related Questions