doris日期维度表

insert into dim.dim_date

select row_number() over ( order by dim.date_value) id,

date_format(dim.date_value,'%Y-%m-%d') date_key,

date_format(dim.date_value,'%Y%m%d') date_value,

day(dim.date_value) day_of_month,

month(dim.date_value) month_value,

date_format(dim.date_value,'%Y%m') year_month,

date_trunc(dim.date_value,'month') month_first,

last_day(dim.date_value) month_end,

weekday(dim.date_value) + 1 day_of_week,

case

when weekday(dim.date_value) = 0 then '星期一'

when weekday(dim.date_value) = 1 then '星期二'

when weekday(dim.date_value) = 2 then '星期三'

when weekday(dim.date_value) = 3 then '星期四'

when weekday(dim.date_value) = 4 then '星期五'

when weekday(dim.date_value) = 5 then '星期六'

when weekday(dim.date_value) = 6 then '星期日'

end week_cn,

case

when weekday(dim.date_value) = 0 then 'Mon'

when weekday(dim.date_value) = 1 then 'Tues'

when weekday(dim.date_value) = 2 then 'Wed'

when weekday(dim.date_value) = 3 then 'Thur'

when weekday(dim.date_value) = 4 then 'Fri'

when weekday(dim.date_value) = 5 then 'Sat'

when weekday(dim.date_value) = 6 then 'Sun'

end week_en,

substr(yearweek(dim.date_value,1),-2) week_num,

yearweek(dim.date_value,1) year_week,

quarter(dim.date_value) season,

year(dim.date_value)*10 + quarter(dim.date_value) season_of_year,

if(month(dim.date_value)>6,2,1) half_year,

date_trunc(dim.date_value,'year') year_first,

days_sub(date_trunc(years_sub(dim.date_value,-1),'year'),1) year_end,

year(dim.date_value) year_num

from (select adddate('1900-01-01', number) date_value from numbers("number" = '73048')) dim -- 73048

-- select datediff('1900-01-01'，'2100-01-01')