Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Get Hive to interpret a timestamp as UTC

Highlighted

Get Hive to interpret a timestamp as UTC

New Contributor

How do I get Hive to interpret a timestamp as UTC?
Assume I want to insert "2017-07-14 02:40:00 UTC" (1500000000 seconds since epoch):

create table timestamp_test(ts timestamp);
insert into timestamp_test select * from (select 1.5e9) t;

However, Hive writes "2017-07-14 04:40:00" into the CSV.
I suppose it interprets the timestamp as CET (timezone of the cluster) and converts it to UTC by adding 2 hours.
Also none of the following works:

insert into timestamp_test select * from (select 1500000000000) t;
insert into timestamp_test select * from (select cast(1.5e9 as timestamp)) t;
insert into timestamp_test select * from (select cast(cast(1.5e9 as timestamp) as string)) t;
insert into timestamp_test select * from (select "2017-07-14 02:40:00") t;
insert into timestamp_test select * from (select cast("2017-07-14 02:40:00" as timestamp)) t;
insert into timestamp_test select * from (select from_utc_timestamp(1.5e9, "CET")) t;
insert into timestamp_test select * from (select from_utc_timestamp(1.5e9, "UTC")) t;
I'm pretty lost. This shouldn't be so complicated.
I basically need to merge a timestamp column of double format into a native timestamp column of another table.
But Hive always does the UTC shift. My version is 1.2.1000.2.6.0.3-8.

Any thoughts?
Many thanks