本地时间将本地时间转换为UTC时间 [英] Local Time Convert To UTC Time In Hive

问题描述

我在互联网上进行了很多搜索，但找不到答案.这是我的问题:

I searched a lot on Internet but couldn't find the answer. Here is my question:

我正在Hive中编写一些查询.我有一个UTC时间戳，想将其更改为UTC时间，例如，给定时间戳1349049600，我想将其转换为UTC时间，即2012-10-01 00:00:00.但是，如果我在Hive中使用内置函数 from_unixtime(1349049600)，则会得到本地PDT时间2012-09-30 17:00:00.

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.

我意识到有一个名为 from_utc_timestamp(timestamp，string timezone)的内置函数.然后我像 from_utc_timestamp(1349049600，"GMT")那样尝试了，输出为1970-01-16 06:44:09.6，这是完全不正确的.

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.

我不想永久更改Hive的时区，因为还有其他用户.那么，有什么方法可以获取从1349049600到"2012-10-01 00:00:00"的UTC时间戳字符串吗?非常感谢！

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!!

推荐答案

据我所知， from_utc_timestamp()需要一个日期字符串参数，例如"2014-01-1511:21:15"，而不是universal-second-epoch以来的值.这可能就是为什么在传递整数时给出奇怪结果的原因?

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?

唯一可以处理历元秒的Hive函数似乎是 from_unixtime()，它为您提供了服务器时区中的时间戳字符串，我在中找到了/etc/sysconfig/clock -就我而言，是美国/蒙特利尔" .

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.

因此您可以通过 to_utc_timestamp(from_unixtime(1389802875)，'America/Montreal')获取UTC时间戳字符串，然后使用 from_utc_timestamp()

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()

这一切似乎都很折磨，特别是必须将服务器TZ连接到SQL中.如果有 from_unixtime_utc()函数之类的东西，生活会更轻松.

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.

更新: from_utc_timestamp()确实处理了 milli seconds参数以及一个字符串，但随后导致转换错误.

Update: from_utc_timestamp() does deal with a milliseconds argument as well as a string, but then gets the conversion wrong.

当我尝试 from_utc_timestamp(1389802875000，'America/Los_Angeles')时，它会给出"2014-01-15 03:21:15" ，这是错误的.
正确答案是"2014-01-15 08:21:15" ，您可以通过 from_utc_timestamp(to_utc_timestamp(from_unixtime(1389802875)，'America/蒙特利尔")，美国/洛杉矶")

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')

这篇关于本地时间将本地时间转换为UTC时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！