# 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.

## 6 thoughts on “HQL which gives number of hours between two timestamps”

1. Nick Kijak says:

I think you’ll have the same problem you did with hours if the two dates are years apart. Jan 1, 2011 – Jan 1, 2010 = ((1*24+11)-(1*24+5))= 35-29=6.

2. alticast says:

Hi!
What about last and 1st days of the month?
((1*24 + 11) – (30*24 +5)) == 35 – 725 = -690 ???

I guess you should consider general case when timestamps have several years inbetween.

3. Andries Inzé says:

Is this still correct when you are in a different month/year?

4. Dimitrios says:

I just tried your method and it’s perfect but I found a huge problem. My two timestamps are using different timezones….

one is NY time and one is UTC…how do I fix this?

5. Harmeet Singh says:

I thin this is work on same month, but if the months are differnet, this approch is fail.