Generate a Time dimension table in MySQL

Generate a Time dimension table in MySQL

Using the MySQL common date function to generate time dimension tables is the most efficient, simplest, and requires no other tools to support. The resulting results are shown for example:

# time Spanset @d0 = "2012-01-01"; SET @d1 = "2012-12-31"; SET @date = date_sub (@d0, Interval 1 day); # Set up the Time dimension tabledrop TABLE IF EXISTS time_dimension; CREATE TABLE ' time_dimension ' (' date ' date default NULL, ' ID ' int not null, ' y ' smallint default null, ' m ' smallint D Efault null, ' d ' smallint default null, ' yw ' smallint default null, ' W ' smallint default null, ' Q ' smallint default NU  LL, ' WD ' smallint default NULL, ' M_name ' char (TEN) default null, ' Wd_name ' char (TEN) default NULL, PRIMARY KEY (' id ')); # Populate the table with Datesinsert to time_dimensionselect @date: = Date_add (@date, Interval 1 day) as date, # in Teger ID that allowsimmediate understanding date_format (@date, "%y%m%d") as ID, year (@date) as Y, month (@date) as M, Day (@date) as D, Date_format (@date, "%x") as YW, week (@date, 3) as W, quarter (@date) as Q, Weekday (@date) +1 as WD, MonthName (@date) as M_name, Dayname (@date) as Wd_namefrom twhere Date_add (@date, INterval 1 day) <= @d1ORDER by date; 

The mysterious table T, just need more than the number of records you need to generate a date. The idea is to select multiple rows of data from the T table and produce the corresponding date fields.

Generate a Time dimension table in MySQL