hive常用的几个日期处理

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 d
    from (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 d
    from (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'))