Support Questions
Find answers, ask questions, and share your expertise

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

2 REPLIES 2

Explorer

Same issue here 😞

 

Any solution?

Community Manager

@dscarlat Have you looked at this post? https://community.cloudera.com/t5/Support-Questions/How-can-I-convert-PST-time-to-UTC-time-in-Hive/t...


Cy Jervis, Manager, Community Program
Was your question answered? Make sure to mark the answer as the accepted solution.
If you find a reply useful, say thanks by clicking on the thumbs up button.
Take a Tour of the Community
Don't have an account?
Your experience may be limited. Sign in to explore more.