目录
一、hive中日期与时间戳转换
1、获取时间戳:unix_timestamp()
2、时间戳->日期:from_unixtime(1441565203,'yyyy/MM/dd HH:mm:ss')
3、日期时间->时间戳:unix_timestamp('2015-09-07 02:46:43')
可以指定时间格式->时间戳:unix_timestamp('20111207 13:01:03','yyyyMMdd HH:mm:ss')
4、输出日期:to_date('2011-12-08 10:03:01')
输出年:year('2011-12-08 10:03:01')
输出月:month('2011-12-08 10:03:01')
输出日期在当前周数:weekofyear('2011-12-08 10:03:01')
year(string date),month(),day(),hour(),minute(),second()
二、Hive常用日期函数整理
1、datediff 返回开始日期减去结束日期的天数:datediff('2015-04-09','2015-04-01')
其中日期是有格式的,目前支持以下两种格式:
‘yyyy-MM-dd HH:mm:ss’
‘yyyy-MM-dd’
2、date_sub 返回日期前n天的日期:date_sub('2015-04-09',4);
3、date_add 返回日期后n天的日期:date_add('2015-04-09',4)
三、Hive列出两个日期之间的所有日期
1、天粒度切割
select
tmp.*,
t.*,
date_add(start_date, pos) as mid_date
from(
select
'1' as uid,
'2020-01-01' as start_date,
'2020-01-05' as end_date
) tmp lateral view posexplode(
split(space(datediff(end_date, start_date)), '')
) t as pos,
val
结果:
2、小时粒度切割
select
from_unixtime(unix_timestamp(start_date) + pos * 3600) as mid_hour
from(
select
'1' as uid,
'2020-01-01 10:00:00' as start_date,
'2020-01-01 15:00:00' as end_date
) tmp lateral view posexplode(
split(
space(
hour(end_date) -hour(start_date) +(
datediff(
end_date,
start_date
)
) * 24
),
''
)
) t as pos,
val
结果:
参考:
https://blog.csdn.net/u013421629/article/details/80450047
https://blog.csdn.net/wrty1993/article/details/78548312