hive常用的几个日期处理

日期格式转化

长日期

-- create_time = 2021-02-05 12:10:58.304
to_date(SUBSTRING(cast(create_time as string),1,10))
2021-01-10

时间戳

--pay_time =1242351140000
to_date( from_unixtime(cast(pay_time/1000 as int)))
2009-05-15

判断支付时间在一年前

变量：pay_time 时间戳格式

cast(substr(to_date( from_unixtime(cast(pay_time /1000 as int))),1,4) as int)=cast(substr(to_date(NOW() ),1,4) as int)-1

判断支付时间小于去年的同一天

变量：pay_time 时间戳格式

to_date( from_unixtime(cast(pay_time/1000 as int)))<=
to_date(substr(from_unixtime(unix_timestamp(concat(cast( (year(to_date( now()))-1) as string),substr(to_date(now()),6,2),substr(to_date(now()),9,2)),'yyyyMMdd')),1,10))

生成截至某一时间的连续日期

输出一年的连续日期

with dates as(select date_add("2020-01-01", a.pos) as dfrom (select posexplode(split(repeat("m", datediff(current_date - interval '1' day, "2021-01-01")), "m"))) a
)
select * from dates

输出前7天连续日期

select date_add(current_date - interval '7' day, a.pos) as dfrom (select posexplode(split(repeat("m", datediff(current_date - interval '1' day, current_date - interval '7' day)), "m"))) a

判断日期在第几个季度

select from_unixtime(unix_timestamp(concat(year('2020-09-09'),case when (floor(substr('2020-09-09',6,2)/3.1)*3)+1<09 then concat(0,(floor(substr('2020-09-09',6,2)/3.1)*3)+1)else (floor(substr('2020-09-09',6,2)/3.1)*3)+1 end,'01'),'yyyyMMdd'))

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！