计算每周第一天和最后一天
select
day
,dayofweek(day) as dw1
,date_add(day,1 - dayofweek(day)) as Su_s -- 周日_start
,date_add(day,7 - dayofweek(day)) as Sa_e -- 周六_end
,case when dayofweek(day) = 1 then 7 else dayofweek(day) - 1 end as dw2
,date_add(day,1 - case when dayofweek(day) = 1 then 7 else dayofweek(day) - 1 end) as Mo_s -- 周一_start
,date_add(day,7 - case when dayofweek(day) = 1 then 7 else dayofweek(day) - 1 end) as Su_e -- 周日_end
,to_date(concat(year(day),'-',lpad(ceil(month(day)/3) * 3 -2,2,0),'-01')) as season_first_day -- 当季第一天
,trunc(day,'MM') as month_first_day -- 当月第一天
,trunc(day,'YY') as year_first_day -- 当年第一天
,last_day(add_months(trunc(day,'YY'),12)) as year_last_day -- 当年最后一天
,last_day(day) as month_last_day -- 当月最后一天
,last_day(to_date(concat(year(day),'-',lpad(ceil(month(day)/3) * 3,2,0),'-01'))) as season_last_day -- 当季最后一天
,weekofyear(day) as weekofyear1 -- 当年第几周
,weekofyear('2019-08-07') - weekofyear(trunc('2019-08-07','MM')) +1 as weekofmonth1 --当月第几周
,second(day) as second -- 秒钟
,minute(day) as minute -- 分钟
,hour(day) as hour -- 小时
,day(day) as day -- 日期
,month(day) as month -- 月份
,lpad(ceil(month(day)/3),2,0) as season -- 季度
,year(day) as year -- 年份
from (
select '2020-04-01' as day union all
select '2020-04-02' as day union all
select '2020-04-03' as day union all
select '2020-04-04' as day union all
select '2020-04-05' as day union all
select '2020-04-06' as day union all
select '2020-04-07' as day union all
select '2020-04-08' as day union all
select '2020-04-09' as day union all
select '2020-04-10' as day union all
select '2020-04-11' as day union all
select '2020-04-12' as day union all
select '2020-04-13' as day union all
select '2020-04-14' as day union all
select '2020-04-15' as day union all
select '2020-04-16' as day union all
select '2020-04-17' as day union all
select '2020-04-18' as day union all
select '2020-04-19' as day union all
select '2020-04-20' as day union all
select '2020-04-21' as day union all
select '2020-04-22' as day
) t1