Search code examples
hivehqlunix-timestamp

Time Difference Calculation in HIVE gives wrong answer

Computing time-difference in HIVE and need it in hours:minutes:seconds. The answer I am getting is not quite right.

This is the query I have tried 

SELECT from_unixtime(UNIX_TIMESTAMP('2019-01-01 12:00:00') - UNIX_TIMESTAMP('2019-01-01 10:10:10'),'hh:mm:ss');

The answer I get is 07:49:50

How do I refactor the code so that I have the answer 01:49:50

Solution

  • @user2510479

    As noted by @mazaneicha from_unixtime function converts to the current system time zone which was causing your result to be 07:49:50 instead of 01:49:50

    To get the expected result (if you prefer to use Hive functions) you can convert from_unixtime output to UTC and use date_format (as of Hive 1.2.0) link on it to get desired output

    SELECT date_format(to_utc_timestamp(from_unixtime( 
      (UNIX_TIMESTAMP('2019-01-01 12:00:00') 
     - UNIX_TIMESTAMP('2019-01-01 10:10:10')) ),'EST'), 'HH:mm:ss')

    You can refer dateformat string in link