[ACCEPTED]-Performing Date/Time Math In HQL?-hql

Accepted answer
Score: 10

Why do you need to do it in the query? Why 2 not just handle it in the java code.

for 1 example:

From RandomThing
Where randomTime is not null and
      randomTime >= :currentTimestamp and
      randomTime <= :maxTimestamp

And then just set the parameters.

Score: 4

You could determine the syntax to do it 13 using SQL in your database and then define 12 a function within a custom HibernateDialect. For 11 example, we needed a weekday function which 10 is not standard SQL. We subclassed the 9 dialect for each database and then added 8 a line like this:

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

In your case, you could 7 use a function called date_diff which might 6 be defined as ? - ? in some databases or 5 something different in others. That way 4 you don't have to write raw SQL in your 3 query and if you ever need to switch databases, you 2 just map the function differently in your 1 dialect.

Score: 3

If you need the database server's time, you 5 could first do a simple hql query to get 4 the timestamp and then calculate the maxTimestamp 3 in java and pass the fetched timestamp and 2 the calculated maxTimeStamp to a query like 1 the one of ccclark.

Score: 1

Does it have to be HQL? I would probably 1 switch up to a hibernate criteria and use:

Criteria.add( Restrictions.SQLRestriction( "{alias} <= current_timestamp() " ) )
Criteria.add( Restrictions.SQLRestriction( "{alias} >= (current_timestamp() + ?) ", 5 )

More Related questions