Performing Date/Time Math In HQL?

Question

I'm looking how to perform date/time math within an HQL query. Specifically, how do I add or subtract (x) amount of time from the result of the current_timestamp() function? Or do I have to drop into SQL for this and hope that whatever database is being run supports it?

HQL query example:

FROM RandomThing
WHERE randomTime IS NOT NULL AND
      randomTime >= current_timestamp() AND
      randomTime <= (current_timestamp() + :timeToAdd)

I can define the :timeToSubtract parameter to be any particular unit, though anything bigger than hours would be undesirable, and seconds would be most desirable.

CLARIFICATION: I realize this can be easily done outside of the query. But for philosophical reasons, let's say it's important to use the database server's time, rather than the querying system's time. Practical example: I'm querying an automatic timestamp for all entries made within the last (x) amount of time. Since the timestamp is made by the database system, it is important to also use the database's current time.

Solution 1

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

Solution 2

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

for example:

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

And then just set the parameters.

Solution 3

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

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

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

Solution 4

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

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

View more solutions

Comments

[James]

I am also looking for the same thing, except that the timeToAdd is not a parameter, but a column from the same row, so it doesn't make sense to make a second query to the same row and table.

[jdmichal]

Good answer. I made clarifications to the question with my response.

[jdmichal]

What is the difference between putting those two statements in criteria versus in an HQL query?

[jdmichal]

I think this is the most generalizable and easy-to-implement solution. Thanks.

[Jeff Evans]

This solution works with the CriteriaQuery API, which is useful if one is already following that approach.

[Quartz]

Because the Java code may not be running on the same clock, and in some queries, even a few seconds difference may cause trouble.