How to get Date Difference in hour using HQL

Question

I've to get difference between current date and the date stored in database in hours. In PostgreSQL, I'm able to do it using the following query:

SELECT storage_time, extract(epoch from (localtimestamp - storage_time))/3600 as diff_hour FROM some_table;

This gives me the difference between dates in hour:

      storage_time       |     diff_hour      
-------------------------+-------------------
 2014-02-03 19:37:39.481 |  44.4788805811111
 2014-02-03 19:40:28.201 |  44.4320139144444
 2014-01-29 18:25:12.828 |  165.686284192222
 2014-02-03 19:25:56.861 |  44.6740528033333
 2014-02-05 15:53:38.178 | 0.212575858888889
 2014-01-30 15:53:38.61  |  144.212455858889

Now, I'm facing difficulty porting it to Hibernate Query Language. I've tried many variations, but I'm unable to get epoch from date difference. This certainly doesn't work:

"FROM SomeEntity " +
"WHERE someAttribute = :attr " +
"AND epoch(localtimestamp - deviceInfo.storageTime) / 3600 > :threshold_hour";

When I run this command, I get following exception:

org.hibernate.exception.SQLGrammarException: could not extract ResultSet
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:500)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:388)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:273)
    at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:56)
    at org.hibernate.loader.Loader.getResultSet(Loader.java:2040)
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1837)
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1816)
    at org.hibernate.loader.Loader.doQuery(Loader.java:900)
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:342)
    at org.hibernate.loader.Loader.doList(Loader.java:2526)
    at org.hibernate.loader.Loader.doList(Loader.java:2512)
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2342)
    at org.hibernate.loader.Loader.list(Loader.java:2337)
    at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:495)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:357)
    at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:195)
    at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1275)
    at org.hibernate.internal.QueryImpl.list(QueryImpl.java:101)

I ran the query like this:

Query query = sessionFactory.getCurrentSession()
                    .getNamedQuery(GET_NON_EXPIRED_ENTITY)
                    .setString("attr", attr)
                    .setInteger("threshold_hour", thresholdHour);

List<SomeEntity> instances = query.list();

Exception is coming at 2nd line, when I call list().

But, when I change epoch() function to day(), in hibernate query, it runs without exception, but of course with unexpected result.

Is there any way I can get the same behavior of PostgreSQL query in HQL?

Answer 1

1. HQL supports EXTRACT(... FROM ...) function, and EPOCH keyword although it is not mentioned in documentstion.
2. HQL also supports no-argument LOCALTIMESTAMP function (for Postgres and MySQL dialects). This fact also is not in documentation, but I found it in sources (link1, link2)

So, required HQL query is:

FROM SomeEntity p 
WHERE EXTRACT(EPOCH FROM (LOCALTIMESTAMP-p.storageTime))/3600 > :threshold