日期格式转化
长日期
-- 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'))