CODEWITHSUNDEEP
scalahadoopapache-sparkhiveimpala
Rob
Rob

Reputation: 162

Impala is converting time into GMT how to avoid that

I'm casting a datefeild into time stamp in hive as from_unixtime(unix_timestamp(), 'yyyy-MM-dd HH:mm:ss') as timestamp) as column_name, when I query it through impala time is displaying in GMT format. Not only the column I created every timestamp field is converting into GMT format.

How to make it display the time as is ? or how can I convert it into est ?

please help with sql query or with the spark/scala code, I tried with this How to convert a Date String from UTC to Specific TimeZone in HIVE? it dint help me.

Upvotes: 3

Views: 4529

Answers (2)

Zoltan
Zoltan

Reputation: 3105

It is not Impala that converts the time to UTC+00:00 but Hive, but only when saving timestamps into Parquet. This is a bug in Hive: HIVE-12767. When reading timestamps from Parquet files, Hive does the reverse adjustment to get the correct value, but Impala does not.

According to the TIMESTAMP Data Type page in Impala's documentation, -convert_legacy_hive_parquet_utc_timestamps=true makes Impala do the same reverse adjustment that Hive does, but using it has a performance overhead.

If you write timestamps with Impala and read them back with Hive, Hive notices that the data was written by Impala thus it requires no adjustment, so timestamps written by Impala show up correctly in Hive.

Upvotes: 1

hlagos
hlagos

Reputation: 7947

did you try this?

SELECT  from_utc_timestamp(from_unixtime(unix_timestamp(), 'yyyy-MM-dd HH:mm:ss'), 'GMT-4')  as column_name;

it should convert your date to EST time zone. Also, considerate the issue regarding to parquet date formats if you will read data instead of use the time system

Impala timestamps don't match Hive - a timezone issue?

Regaards!

Upvotes: 0

Related Questions