如何将日期字符串从 UTC 转换为 HIVE 中的特定时区? [英] How to convert a Date String from UTC to Specific TimeZone in HIVE?
问题描述
我的 Hive 表有一个带有 UTC 日期字符串的日期列.我想获取特定 EST 日期的所有行.
My Hive table has a date column with UTC date strings. I want to get all rows for a specific EST date.
我正在尝试执行以下操作:
I am trying to do something like the below:
Select *
from TableName T
where TO_DATE(ConvertToESTTimeZone(T.date)) = "2014-01-12"
我想知道是否有 ConvertToESTTimeZone 的函数,或者如何实现?
I want to know if there is a function for ConvertToESTTimeZone, or how I can achieve that?
我尝试了以下方法但不起作用(我的默认时区是 CST):
I tried the following but it doesnt work (my default timezone is CST):
TO_DATE(from_utc_timestamp(T.Date) = "2014-01-12"
TO_DATE( from_utc_timestamp(to_utc_timestamp (unix_timestamp (T.date), 'CST'),'EST'))
提前致谢.
更新:
Strange behavior. When I do this:
select "2014-01-12T15:53:00.000Z", TO_DATE(FROM_UTC_TIMESTAMP(UNIX_TIMESTAMP("2014-01-12T15:53:00.000Z", "yyyy-MM-dd'T'hh:mm:ss.SSS'Z'"), 'EST'))
from TABLE_NAME T1
limit 3
我明白了
_c0 _c1
0 2014-01-12T15:53:00.000Z 1970-01-16
1 2014-01-12T15:53:00.000Z 1970-01-16
2 2014-01-12T15:53:00.000Z 1970-01-16
推荐答案
您的系统时区 CST 与在 Hive 中将 UTC 转换为 EST 无关.您应该能够获得正确的结果:
Your system timezone CST doesn't matter for converting UTC to EST in Hive. You should be able to get the correct results with:
TO_DATE(FROM_UTC_TIMESTAMP(UNIX_TIMESTAMP(T.date, "yyyy-MM-dd'T'hh:mm:ss.SSS'Z'") * 1000, 'EST'))
请注意,因为 UNIX_TIMESTAMP
返回秒,您将丢失时间戳的毫秒部分.
Note that because UNIX_TIMESTAMP
returns seconds, you will lose the millisecond component of your timestamp.
这篇关于如何将日期字符串从 UTC 转换为 HIVE 中的特定时区?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!