如何通过在Hive中保留数据中的毫秒数将UTC转换为CST时间戳 [英] How to convert UTC to CST timestamp by retaining the milliseconds in data in Hive

问题描述

我有一个配置单元表，其中数据类型为timestamp的数据为UTC格式.我在此表的顶部创建了一个视图，以将夏令时的 UTC 转换为 CST .这种转换工作正常.但是数据丢失了毫秒.

I have a hive table in which the data with datatype timestamp is in UTC format. I have created a view on top of this table to convert UTC to CST with daylight savings. That conversion is working fine. But the data is missing milliseconds in it.

以下逻辑用于将UTC转换为CST

The below logic is used in view to convert UTC to CST

FROM_UTC_TIMESTAMP(UNIX_TIMESTAMP(added_date, 'yyyy-MM-dd HH:mm:ss.SSS') * 1000, 'CST6CDT')

请帮助我将 UTC 转换为 CST ，包括时间戳的毫秒数.

Please help me to convert UTC to CST including the milliseconds in timestamp.

推荐答案

unix_timestamp()函数忽略毫秒.它返回从unix纪元过去的秒数.毫秒丢失.例如:

unix_timestamp() function ignores milliseconds. it returns seconds passed from unix epoch. Milliseconds are lost. For example:

 select FROM_UTC_TIMESTAMP(UNIX_TIMESTAMP('2020-01-21 15:15:35.125', 'yyyy-MM-dd HH:mm:ss.SSS') * 1000, 'CST6CDT');

返回:

2020-01-21 09:15:35

如果时间戳采用正确的格式'yyyy-MM-dd HH:mm:ss.SSS'(在您的示例中，根据模板，则不需要转换)，效果很好:

If the timestamp is in the right format 'yyyy-MM-dd HH:mm:ss.SSS' (and in your example, according to the template, it does not require conversion), this works fine:

with s as (select '2020-01-21 15:15:35.125' as added_date) 

select FROM_UTC_TIMESTAMP(s.added_date, 'CST6CDT')  from s;

结果:

2020-01-21 09:15:35.125

如果时间戳是需要转换的格式，解决方法是从原始时间戳中提取毫秒，并将unix_timestamp乘以1000后得到的毫秒数加起来:

And if the timestamp is in the format which requires conversion, the workaround is to extract milliseconds from original timestamp and add to the milliseconds you got after multiplying unix_timestamp by 1000:

with s as (select '2020-01-21 15:15:35.125' as added_date) 

select FROM_UTC_TIMESTAMP(UNIX_TIMESTAMP(s.added_date, 'yyyy-MM-dd HH:mm:ss.SSS') * 1000+cast(split(added_date,'\\.')[1] as int), 'CST6CDT')  from s;

结果:

2020-01-21 09:15:35.125

另一种解决方法是将毫秒级连接:

with s as (select '2020-01-21 15:15:35.125' as added_date) 

select concat_ws('.',cast(FROM_UTC_TIMESTAMP(UNIX_TIMESTAMP(s.added_date, 'yyyy-MM-dd HH:mm:ss.SSS') * 1000, 'CST6CDT') as string),split(added_date,'\\.')[1]) from s;

结果:

2020-01-21 09:15:35.125

这篇关于如何通过在Hive中保留数据中的毫秒数将UTC转换为CST时间戳的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！