from_unixtime https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF总是直到minutes(yyyy-MM-dd HH:mm:ss)
to get millisecs
我们需要采取一些解决方法。
- 我们将提取
millisecs
从old_time开始使用regexp_extract
then concat
那个到from_unixtime
结果并最终投射到timestamp
.
Example:
select old_time,
timestamp(concat_ws(".", --concat_ws with . and cast
FROM_UNIXTIME(UNIX_TIMESTAMP(old_time, "yyyy-MM-dd'T'HH:mm:ss.SSS'Z'"),"yyyy-MM-dd HH:mm:ss"), -- from_unixtime and unix_timestamp to convert without millisecs
regexp_extract(string(old_time),".+\\.(.*)(?i)z",1))) as newtime from --regexp_extract to extract last 3 digits before z then concat
(select string("2020-03-11T21:14:41.335Z")old_time)e
+------------------------+-----------------------+
|old_time |newtime |
+------------------------+-----------------------+
|2020-03-11T21:14:41.335Z|2020-03-11 21:14:41.335|
+------------------------+-----------------------+
UPDATE:
您的样本数据有:
在毫秒之前,尝试使用以下查询:
select old_time,
timestamp(concat_ws(".", --concat_ws with . and cast
FROM_UNIXTIME(UNIX_TIMESTAMP(old_time, "yyyy-MM-dd'T'HH:mm:ss:SSS'Z'"),"yyyy-MM-dd HH:mm:ss"), -- from_unixtime and unix_timestamp to convert without millisecs
regexp_extract(string(old_time),".+\\:(.*)(?i)z",1))) as newtime from --regexp_extract to extract last 3 digits before z then concat
(select string("2020-03-11T21:14:41:335Z")old_time)e