Hive字符串到时间戳的转换,以毫秒为单位 [英] Hive String to Timestamp conversion with Milliseconds
本文介绍了Hive字符串到时间戳的转换,以毫秒为单位的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我需要转换上述输入字符串格式,并在时间戳中生成所需的输出,如下所示.
I have a requirement to convert the mentioned input string format and produce the desired output in timestamp as shown below.
输入:16AUG2001:23:46:32.876086
所需的输出: 2001-08-16 23:46:32.876086
通过运行以下代码获得的输出:2001-08-17 00:01:08
Output which is coming by running the below code: 2001-08-17 00:01:08
查询:
select '16AUG2001:23:46:32.876086' as row_ins_timestamp,
from_unixtime(unix_timestamp('16AUG2001:23:46:32.876086',
'ddMMMyyyy:HH:mm:ss.SSSSSS')) as row_ins_timestamp
from temp;
毫秒部分未按要求转换.请提出建议.
Milliseconds part is not getting converted as required. Please suggest.
推荐答案
unix_timestamp函数不保留毫秒. 不用毫秒转换,然后用毫秒部分连接:
unix_timestamp function does not preserve milliseconds. Convert without milliseconds, then concatenate with millisecond part:
with your_data as (
select stack(3,
'16AUG2001:23:46:32.876086',
'16AUG2001:23:46:32',
'16AUG2001:23:46:32.123'
) as ts
)
select concat_ws('.',from_unixtime(unix_timestamp(split(ts,'\\.')[0],'ddMMMyyyy:HH:mm:ss')),split(ts,'\\.')[1])
from your_data;
结果:
2001-08-16 23:46:32.876086
2001-08-16 23:46:32
2001-08-16 23:46:32.123
Time taken: 0.089 seconds, Fetched: 3 row(s)
这篇关于Hive字符串到时间戳的转换,以毫秒为单位的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文