在Hive HQL中将字符串转换为时间戳 [英] Convert String to Timestamp in Hive HQL
问题描述
我有一个类似"08/03/2018 02:00:00"的字符串,我正在尝试将其转换为时间戳记值.
I'm having a string like "08/03/2018 02:00:00" which i'm trying to convert into a timestamp value.
我正在使用以下代码:
unix_timestamp("08/03/2018 02:00:00", "yyyy-MM-dd'T'HH:mm:ss.SSSXXX")
当我使用上面的代码时,它会抛出一个NULL值.
when i use the above code it's throwing a NULL value.
如何在Hive/Hue Editor中将此字符串转换为Timestamp?
How can i convert this string to Timestamp in Hive/Hue Editor?
推荐答案
您指定的格式与实际时间戳不匹配.
如果您的示例中的08/03
是dd/MM
,则:
The format you specified does not match to the actual timestamp.
If 08/03
in your example is dd/MM
then:
select unix_timestamp("08/03/2018 02:00:00", "dd/MM/yyyy HH:mm:ss")
OK
1520503200
Time taken: 0.299 seconds, Fetched: 1 row(s)
select from_unixtime(unix_timestamp("08/03/2018 02:00:00", "dd/MM/yyyy HH:mm:ss"))
OK
2018-03-08 02:00:00
Time taken: 0.068 seconds, Fetched: 1 row(s)
如果要从ISO时间戳转换,请参见以下答案 https://stackoverflow.com/a/23520257/2700344
See this answer if you want convert from ISO timestamp https://stackoverflow.com/a/23520257/2700344
您可以为unix_timestamp指定日期格式(非标准格式).在此处查看文档: https://cwiki.apache.org/confluence/display/Hive/LanguageManual + UDF#LanguageManualUDF-DateFunctions
You can specify date pattern for unix_timestamp for non-standard format. See docs here: https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-DateFunctions
这篇关于在Hive HQL中将字符串转换为时间戳的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!