mysql日期纬度表_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为一张数据库中数据量比较大的表