Hive時間轉(zhuǎn)換

用到hive時間轉(zhuǎn)換總結(jié)如下:

轉(zhuǎn)換成小時

req_time是 "2016-11-29 17:47:59"
要轉(zhuǎn)換成小時"2016-11-29 17:00:00"
用到的函數(shù)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

運(yùn)行結(jié)果

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

時間戳轉(zhuǎn)日期

t是毫秒時間戳--1545840065339 from_unixtime第一個參數(shù)是bigint類型，通過cast轉(zhuǎn)換下
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

結(jié)果

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時區(qū)轉(zhuǎn)換

時間格式 28/Mar/2019:11:14:47 +0800 需要轉(zhuǎn)換成 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這樣分格式可以采用下面的方式先轉(zhuǎn)為數(shù)字

 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,
            ...

然后采用下面的方式轉(zhuǎn)換

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