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

    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.

    Reply
  2. alticast

    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.

    Reply
  3. Pingback: Tweets that mention HQL which gives number of hours between two timestamps « My Thoughts on software development -- Topsy.com

  4. Dimitrios

    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?

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s