mysql时间维度表。

# time span  
SET @d0 = "2012-01-01";
SET @d1 = "2030-12-31";
SET @date = date_sub(@d0, INTERVAL 1 DAY);
# set up the time dimension table  
DROP 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 dates  
INSERT INTO time_dimension SELECT
    @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_name
FROM
    t
WHERE
    date_add(@date, INTERVAL 1 DAY) <= @d1
ORDER BY
    date;

注意：t为一张数据库中数据量比较大的表