如何将ISO日期转换为Hive中的UTC日期 [英] How to convert ISO Date to UTC date in Hive
问题描述
我的JSON数据如下所示:我需要将该日期或 mongo_date
转换为utc时间戳,以便按照每年,每月的时间线示例分析hive中的数据,每周使用map reduce
$ $ $ $ b $ _id:ObjectId(51ac77050e9edcdad271ce2d),
company:null,
date:19760224,
mongo_date:ISODate(1976-02-24T00:00:00Z)
Hive理解这种格式:'yyyy-MM-dd HH:mm:ss.SSS'。对于UTC时间戳,您可以通过
进行转换,只需用空格('')替换'T'并移除'Z':
<$ c $ ('2014-01-29T18:15:12.429Z',1,10),'',
substr('2014-01-29T18:15: 12.429Z',12,12)
),'yyyy-MM-dd HH:mm:ss.SSS')
)
找到了更优雅的解决方案。
select from_unixtime(UNIX_TIMESTAMP(2017-01-01T05:01:10Z,yyyy-MM-dd'T' HH:mm:ss'Z'),yyyy-MM-dd HH:mm:ss);
OK
2017-01-01 05:01:10
看到这个: jira
I have JSON data as below: I need to convert that date or mongo_date
into utc timestamp, to analyse the data in hive as per timeline example per year, per month, per week using map reduce
{
"_id" : ObjectId("51ac77050e9edcdad271ce2d"),
"company" : null,
"date" : "19760224",
"mongo_date" : ISODate("1976-02-24T00:00:00Z")
Hive understands this format: 'yyyy-MM-dd HH:mm:ss.SSS'. For UTC timestamp you can convert by simply replacing 'T' with space (' ') and removing 'Z':
from_unixtime(unix_timestamp(
concat(substr('2014-01-29T18:15:12.429Z',1,10),' ',
substr('2014-01-29T18:15:12.429Z',12,12)
),'yyyy-MM-dd HH:mm:ss.SSS')
)
Have found more elegant solution.
select from_unixtime(UNIX_TIMESTAMP("2017-01-01T05:01:10Z", "yyyy-MM-dd'T'HH:mm:ss'Z'"),"yyyy-MM-dd HH:mm:ss");
OK
2017-01-01 05:01:10
See this: jira
这篇关于如何将ISO日期转换为Hive中的UTC日期的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!