Local Time Convert To UTC Time In Hive
hive, timestamp, utc
Solution
As far as I can tell, `from_utc_timestamp()` needs a date string argument, like `"2014-01-15 11:21:15"`, not a unix seconds-since-epoch value. That might be why it is giving odd results when you pass an integer?
The only Hive function that deals with epoch seconds seems to be `from_unixtime()` which gives you a timestamp string in the server timezone, which I found in `/etc/sysconfig/clock` - `"America/Montreal"` in my case.
So you can get a UTC timestamp string via `to_utc_timestamp(from_unixtime(1389802875),'America/Montreal')`, and then convert to your target timezone with `from_utc_timestamp()`
It all seems very torturous, particularly having to wire your server TZ into your SQL. Life would be easier if there was a `from_unixtime_utc()` function or something.
Update: `from_utc_timestamp()` does deal with a milliseconds argument as well as a string, but then gets the conversion wrong.
When I try `from_utc_timestamp(1389802875000, 'America/Los_Angeles')` it gives `"2014-01-15 03:21:15"` which is wrong. The correct answer is `"2014-01-15 08:21:15"` which you can get (for a server in Montreal) via `from_utc_timestamp(to_utc_timestamp(from_unixtime(1389802875),'America/Montreal'), 'America/Los_Angeles')`
Problem
I searched a lot on Internet but couldn't find the answer. Here is my question: I'm writing some queries in Hive. I have a UTC timestamp and would like to change it to UTC time, e.g., given timestamp 1349049600, I would like to convert it to UTC time which is 2012-10-01 00:00:00. However if I use the built in function `from_unixtime(1349049600)` in Hive, I get the local PDT time 2012-09-30 17:00:00. I realized there is a built in function called `from_utc_timestamp(timestamp, string timezone)`. Then I tried it like `from_utc_timestamp(1349049600, "GMT")`, the output is 1970-01-16 06:44:09.6 which is totally incorrect. I don't want to change the time zone of Hive permanently because there are other users. So is there any way I can get a UTC timestamp string from 1349049600 to "2012-10-01 00:00:00"? Thanks a lot!!