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
...
19/Nov/2019:16:18:19 +0800这样分格式可以采用下面的方式先转为数字
select
case
when time_local like '%Jan%' then regexp_replace(time_local, 'Jan', '01')
when time_local like '%Feb%' then regexp_replace(time_local, 'Feb', '02')
when time_local like '%Mar%' then regexp_replace(time_local, 'Mar', '03')
when time_local like '%Apr%' then regexp_replace(time_local, 'Apr', '04')
when time_local like '%May%' then regexp_replace(time_local, 'May', '05')
when time_local like '%Jun%' then regexp_replace(time_local, 'Jun', '06')
when time_local like '%Jul%' then regexp_replace(time_local, 'Jul', '07')
when time_local like '%Aug%' then regexp_replace(time_local, 'Aug', '08')
when time_local like '%Sep%' then regexp_replace(time_local, 'Sep', '09')
when time_local like '%Oct%' then regexp_replace(time_local, 'Oct', '10')
when time_local like '%Nov%' then regexp_replace(time_local, 'Nov', '11')
when time_local like '%Dec%' then regexp_replace(time_local, 'Dec', '12')
else time_local
end as req_time,
...
然后采用下面的方式转换
select
from_unixtime(
unix_timestamp(req_time, 'dd/MM/yyyy:HH:mm:ss +0800'),
'yyyy-MM-dd HH:00:00'
) as req_time,
...
