CODEWITHSUNDEEP
hadoophiveimpala
Joha
Joha

Reputation: 965

How can i get the same result as Hives from_unixtime in Impala?

I understand Imapla interpretes timestamps relative to the UTC timezone. I also know there is a setting where this behavior can be changed, so that impala interpretes timestamps relative to the local timezone. The problem is I cannot change this setting.

Is there a way to get the same result as i get in Hive in Impala? Here is the Hive result:

select from_unixtime(1521938294)
--> 2018-03-25 01:38:14

The local timezone is Europe/Vienna

Upvotes: 0

Views: 758

Answers (1)

Gomz
Gomz

Reputation: 858

Great question. But the answer is tricky! I was researching this for sometime and below is my answer.

  1. The date and time selected by you points to the time when the time shift happens for Vienna. i.e., the time gets shifted ahead by one hour.

You can check it by using the below links.

HERE1

HERE2

  1. Coming to the time conversion in Impala without using any flags as you need, the syntax would like the below, 
select from_utc_timestamp(cast(from_unixtime(1521938294) AS TIMESTAMP), 'Europe/Vienna');
[OR]
select from_utc_timestamp('2018-03-25 00:38:14', 'Europe/Vienna');

2018-03-25 01:38:00   <---EXPECTED OUTPUT
2018-03-25 02:38:00   <---ACTUAL OUTPUT IN IMPALA

The above syntax does the job of converting the Unix Timestamp into Europe/Vienna Time, However there is a difference in the one hour difference in the result as shown above due to the difference between the time shift that happens in real life happens vs the conversion that happens in Impala.

Reason:

Time shift in Real life - 25th March, 2019 01:59:59 and then 25th March, 2019 03:00:00

Time shift in Impala - 25th March, 2019 00:59:59 and then 25th March, 2019 02:00:00

Even though there seems to be a conversion issue in Impala, the syntax I provided above should work flawlessly for any dates in the year 2018 other than 25-March.

For Other dates:

select from_utc_timestamp('2018-03-26 00:38:14', 'Europe/Vienna')

2018-03-26 02:38:14   <---ACTUAL & CORRECT OUTPUT IN IMPALA

Hope it helps!

Upvotes: 2

Related Questions