本地时间转换为 Hive 中的 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"，不是 Unix 纪元以来的秒数.这可能就是为什么当你传递一个整数时它会给出奇怪的结果?

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 - "America/Montreal" 就我而言.

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 秒参数以及一个字符串，但随后会导致转换错误.

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

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