Hive时间转换

link之家

链接快照平台

输入网页链接，自动生成快照
标签化管理网页链接

相关文章推荐

高大的电脑桌 · 43岁奥兰多·布鲁姆去滑雪！《魔戒》精灵王子 ...· 4 月前 ·

酒量小的针织衫 · 王东伟 - 青岛农业大学 - 机电工程学院· 4 月前 ·

威武的烤面包 · 如何评价切尸红人魔的漫画海门回声？ - 知乎· 1 年前 ·

温暖的弓箭 · 狼人杀-因为嫌烦所以全点了敏捷-漫画牛· 1 年前 ·

朝气蓬勃的豌豆 · 国有车企何时停止“放卫星”？_腾讯新闻· 1 年前 ·

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
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
                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'),