MySQL Time dimension table

1. Build a tabletest_datefor the self-growth of the date, only one column inside .ID, write a store, insert sequential numbers

Begin#routine body goes here...declare ID int;set id=1;while id<=6000 doinsert into test_datevalues (ID); set id=id+1;en D while; END

2. Create a Time dimension table, insert a record

set  @d0  =  "2000-01-01"; set  @d1  =  "2020-12-31"; set  @str  =  "Q"; set  @date  =  Date_sub (@d0,  interval 1 day);insert into dim_timeselect  @date  := date _add (@date,  interval 1 day),       year (@date),   month (@ Date),   dayofyear (@date),   day (@date)  as d,  weekday (@date) +1 as  wd,  week (@date,  3)  as w,  dayname (@date)  as wd_name,   monthname (@date)  as m_name,   quarter (@date)  as q,  concat ( @str, Quarter (@date))  as qq,  dayofmonth (Last_day (@date)),   date_format (@date,   "%y%m%d"),       date_format (@date,  '%d-%m-%y ') as ywfrom  Test_datewhere date_add (@date,  interval 1 day)  <=  @d1ORDER &nbsp by id;

MySQL Time dimension table