HQL which gives number of hours between two timestamps

Intent

To write down a hql (hibernate query language) which gives the difference between two timestamps.

Motivation

Today, i had to write down a hql which should return me the difference between two timestamps. It took me quite a long time to write down the hql query which gives me the correct result. So, i am writing down hql in this blog in order to help any developer who might face this problem.To explain the problem, suppose we have to find out the number of hours before which a user was last updated. The problem seemed quite easy when I first thought about the solution.  The most obvious solution i thought was to use hour() function of hql. So, I wrote down the hql

select hour(current_timestamp – user.lastUpdatedTimeStamp) from com.test.User as user where user.id=1;

It worked but only when both the timestamps are of the same date i.e. if the current_timestamp and lastUpdateTimeStamp are of same date (18th April 2010). This solution does not work if the timestamp are on different date as hour function gives number of hours based on time only, it does not consider dates. So, if current_timestamp is 19-04-2010 11:00:00 and lastUpdatedTimeStamp is 18-04-2010 5:00:00 , it will give answer as 6 which is wrong as number of hour between these timestamps is 30.

Solution

HQL which works is

select

(days(current_timestamp) *24 + hour(current_timestamp)) -( days(user.lastUpdatedTimeStamp)*24 + hour( user.lastUpdatedTimeStamp))

from com.test.User as user where user.id=1;

This hql will give the correct answer as it will add the number of hours corresponding to the date to the number of hours on that particular date.

Mathematically, this will be like this

((19*24 + 11)  -(18*24 +5)) == 467 – 437 = 30

So, we will get the correct answer 30.