首先,我们先了解13位时间戳
,13位的时间戳
其实数据的时间单位
就是ms
。基于此我们来分析问题原因和实现方式。
要点:
13位时间戳因为时间单位为毫秒所以我们可以很容易知道转化成标准毫秒时间格式毫秒部分必然与13位时间戳一致。
举个例子方便大家理解。
bigint(13):1571709884123
毫秒时间标准格式:2019-10-22 10:22:44.123
解决方案:
首选方案:
那么就此我们开始分析我们该如何实现,首先容易想到的就是
hive
中默认的转换函数FROM_UNIXTIME(unix_timestamp/1000,format)
。我们知道unix_timestamp
时间戳的精度默认为s
,而我们要转为ms
所以将unix_timestamp/1000
来将单位转换为ms
。format我们设置成时间格式'yyyy-MM-dd HH:dd:ss.sss'
为毫秒。
SELECT FROM_UNIXTIME(1571709884123/1000,'yyyy-MM-dd HH:dd:ss.sss');
2019-10-22 10:22:44.044
说明:
13位的整型除以1000
等于时间戳timestamp
单位才是毫秒,将时间戳类型转化为日期类型使用from_unixtime
FROM_UNIXTIME(unix_timestamp/1000,format)
参数介绍:
unix_timestamp
:单位为秒
unix_timestamp/1000
:单位为毫秒。
format
:yyyy-MM-dd HH:dd:ss.sss
(毫秒)、yyyy-MM-dd HH:dd:ss
(秒)、yyyy-MM-dd
(日期)等
我们发现毫秒值在转换时毫秒部分并不准确。这是什么原因呢?笔者经过调研发现可能为如下原因造成。
原因:
Java
的date
默认精度是毫秒
,由date
转换成的时间戳是13
位的,而c,php
生成的时间默认就是10
位的,精度是秒
。
方案调整:
由于
hive
本身自带的时间戳转换函数毫秒存在精度问题无法使用,所以我们需要想办法解决毫秒部分的精度问题。由此我们发现只有毫秒部分存在问题而秒及以上的部分是没有问题,所以我们使用FROM_UNIXTIME(unix_timestamp/1000,format)
函数并将毫秒部分拼接在from_unixtime
函数结果之后。
select
concat(t,".",substring(1571709884123,11,13))
from(
select from_unixtime(cast(substring(1571709884123,0,10) as bigint),'yyyy-MM-dd HH:dd:ss') as t
)tmp;
执行结果如下:
扩展:
如果想转换成"yyyy-MM-dd HH:dd:ss",需要将13位时间戳转换成11位时间戳,注意转换过程中函数入参数据类型