The first step is to create the function f_dim_day
CREATE PROCEDURE F_dim_day (in Start_date VARCHAR (a), in Date_count int)
Begin
declare i int;
Set i=0;
DELETE from Dim_day;
While I<date_count do
INSERT into Dim_day
(Day_id,day_short_desc,day_long_desc,week_id,week_long_desc,month_id,month_short_desc,month_long_desc,quarter_ ID,QUARTER_LONG_DESC,YEAR_ID,YEAR_LONG_DESC)
SELECT
REPLACE (start_date, '-', ') day_id,
Date_format (Str_to_date (start_date, '%y-%m-%d%h:%i:%s '), '%y-%m-%d ') Day_short_desc,
Date_format (Str_to_date (start_date, '%y-%m-%d%h:%i:%s '), '%Y year%m month%d day ') Day_long_desc,
Date_format (Str_to_date (start_date, '%y-%m-%d%h:%i:%s '), '%y%u ') week_id,
Date_format (Str_to_date (start_date, '%y-%m-%d%h:%i:%s '), '%Y year%u week ') Week_long_desc,
Date_format (Str_to_date (start_date, '%y-%m-%d%h:%i:%s '), '%y%m ') month_id,
Date_format (Str_to_date (start_date, '%y-%m-%d%h:%i:%s '), '%y-%m ') Month_short_desc,
Date_format (Str_to_date (start_date, '%y-%m-%d%h:%i:%s '), '%Y-year%m month ') Month_long_desc,
CONCAT (Date_format (Str_to_date (start_date, '%y-%m-%d%h:%i:%s '), '%Y '), Quarter (Str_to_date (start_date, '%y-%m-%d%H :%i:%s ')) quarter_id,
CONCAT (Date_format (Str_to_date (start_date, '%y-%m-%d%h:%i:%s '), '%Y '), ' The Year ', Quarter (str_to_date (start_date, '%y-% m-%d%h:%i:%s '), ' quarter ') Quarter_long_desc,
Date_format (Str_to_date (start_date, '%y-%m-%d%h:%i:%s '), '%Y ') year_id,
Date_format (Str_to_date (start_date, '%y-%m-%d%h:%i:%s '), '%Y year ') Year_long_desc
from dual;
Set i=i+1;
Set start_date = Date_format (Date_add (Str_to_date (start_date, '%y-%m-%d%h:%i:%s '), Interval 1 day), '%y-%m-%d ');
End while;
End
In the second step, the calling function passes the start time and the number of days, executing the following sql:
Call F_dim_day (' 2015-01-01 ', 365)
The resulting effect is as follows:
MySQL Build date dimension table
