How to perform date operations in hibernate

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.

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

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

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