Hive时间转换

Hive时间转换

---

最近使用到hive时间转换很频繁，总结如下:

转换成小时

req_time是 "2016-11-29 17:47:59"
要转换成小时"2016-11-29 17:00:00"
用到的函数from_unixtime和unix_timestamp

set mapreduce.job.priority=VERY_HIGH;
select
t.pvid,
from_unixtime(unix_timestamp(t.ext['req_time'],'yyyy-MM-dd HH')),
from_unixtime(unix_timestamp(t.ext['click_time'],'yyyy-MM-dd HH')),
substr(t.ext['req_time'],0,13) req_time,
substr(t.ext['click_time'],0,13) click_time
from 
table_xxx t
where logtype=3
and dt = '20181111'
limit 100

运行结果

9168f033-0df8-4304-82b8    2018-11-11 12:00:00 2018-11-11 12:00:00 2018-11-11 12   2018-11-11 12
a0b73e83-9f34-476c-baaa    2018-11-11 13:00:00 2018-11-11 13:00:00 2018-11-11 13   2018-11-11 13
ff186d99-8259-474c-9e6e    2018-11-11 09:00:00 2018-11-11 09:00:00 2018-11-11 09   2018-11-11 09
a87a6db4-3bf3-4d4e-a585    2018-11-08 21:00:00 2018-11-11 19:00:00 2018-11-08 21   2018-11-11 19
abf720cd-7ee2-466c-90a9    2018-11-11 08:00:00 2018-11-11 08:00:00 2018-11-11 08   2018-11-11 08
c0fea778-5e4d-4b17-9ec1    2018-11-11 18:00:00 2018-11-11 18:00:00 2018-11-11 18   2018-11-11 18

时间戳转日期

t是毫秒时间戳--1545840065339 from_unixtime第一个参数是bigint类型，通过cast转换下
from_unixtime(cast(t/1000 as bigint), 'yyyy-MM-dd HH:mm:ss')

select distinct  from_unixtime(cast(t/1000 as bigint), 'yyyy-MM-dd HH:mm:ss') from test_date; 
2018-12-17 02:46:43

时间戳格式化到小时

select
from_unixtime(cast(t/1000 as bigint), 'yyyy-MM-dd HH:00:00') as server_time,
from_unixtime(cast(kv['__sts__'] as bigint), 'yyyy-MM-dd HH:00:00') as user_time
from table_xxx
where dt='{@date}' limit 10

结果

2019-06-23 00:00:00 2019-06-22 23:00:00
2019-06-23 00:00:00 2019-06-22 23:00:00
2019-06-23 00:00:00 2019-06-22 23:00:00
2019-06-23 00:00:00 2019-06-22 23:00:00
2019-06-23 00:00:00 2019-06-22 23:00:00
2019-06-23 00:00:00 2019-06-22 23:00:00
2019-06-23 00:00:00 2019-06-22 23:00:00
2019-06-23 00:00:00 2019-06-22 23:00:00
2019-06-23 00:00:00 2019-06-22 23:00:00
2019-06-23 00:00:00 2019-06-22 23:00:00

+8时区转换

时间格式 28/Mar/2019:11:14:47 +0800 需要转换成 2019-3-28 11:14:47

select
from_unixtime(
                unix_timestamp(time, 'dd/MMM/yyyy:HH:mm:ss +0800'),
                'yyyy-MM-dd HH:mm:sss'
            ) as req_time
...