Mysql generates a time dimension table. mysql generates a dimension table.
Generate a time dimension table in mysql
Using mysql common date functions to generate a time dimension table is the most efficient and simple, without the support of other tools. Example of result generation:
# 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 DEFAULT NULL, `d` smallint DEFAULT NULL, `yw` smallint DEFAULT NULL, `w` smallint DEFAULT NULL, `q` smallint DEFAULT NULL, `wd` smallint DEFAULT NULL, `m_name` char(10) DEFAULT NULL, `wd_name` char(10) DEFAULT NULL, PRIMARY KEY (`id`)); # populate the table with datesINSERT INTO time_dimensionSELECT @date := date_add(@date, interval 1 day) as date, # integer 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 only needs to have more records than the date you need to generate. The idea is to select multiple rows of data from the T table and generate the corresponding date field at the same time.