如何将日期字符串从UTC转换为HIVE中的特定时区? [英] How to convert a Date String from UTC to Specific TimeZone in HIVE?
问题描述
我正在尝试执行下面的操作:
从TableName中选择*
T
其中TO_DATE(ConvertToESTTimeZone(T.date))=2014-01-12
我想知道是否有一个ConvertToESTTimeZone函数,或者我可以实现这个功能吗?
我尝试了以下方法,但它不起作用(我的默认时区是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'))
预先致谢。
更新:
奇怪的行为当我这样做时:
选择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'))TABLE_NAME中的
T1
限制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无关紧要。您应该能够得到正确的结果:
TO_DATE(FROM_UTC_TIMESTAMP(UNIX_TIMESTAMP(T.date,yyyy-MM- dd'T'hh:mm:ss.SSS'Z')* 1000,'EST'))
请注意,由于
UNIX_TIMESTAMP
会返回秒,因此您将失去时间戳的毫秒组分。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"
I want to know if there is a function for ConvertToESTTimeZone, or how I can achieve that?
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'))
Thanks in advance.
Update:
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
I get
_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
解决方案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'))
Note that because
UNIX_TIMESTAMP
returns seconds, you will lose the millisecond component of your timestamp.这篇关于如何将日期字符串从UTC转换为HIVE中的特定时区?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!