以毫秒为单位的 Hive 字符串到时间戳转换 [英] Hive String to Timestamp conversion with Milliseconds

问题描述

我需要转换上述输入字符串格式并在时间戳中生成所需的输出，如下所示.

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屋！