SET @d0 = '2018-01-01'; SET @d1 = '2018-12-31'; SET @date = DATE_SUB(@d0, INTERVAL 1 DAY); USE test; CREATE TABLE aa_time_dim( DATE DATE DEFAULT NULL, id INT(11) NOT NULL, Y SMALLINT(6) DEFAULT NULL, m SMALLINT(6) DEFAULT NULL, d SMALLINT(6) DEFAULT NULL, yw SMALLINT(6) DEFAULT NULL, w SMALLINT(6) DEFAULT NULL, q SMALLINT(6) DEFAULT NULL, wd SMALLINT(6) DEFAULT NULL, m_name CHAR(10) DEFAULT NULL, wd_name CHAR(10) DEFAULT NULL, PRIMARY KEY (id))ENGINE = INNODB DEFAULT CHARSET = utf8; SELECT * FROM aa_time_dim; INSERT INTO aa_time_dim SELECT @date := DATE_ADD(@date, INTERVAL 1 DAY) AS DATE, 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 xxx WHERE DATE_ADD(@date,INTERVAL 1 DAY) <= @d1 ORDER BY DATE; SELECT * FROM aa_time_dim; DROP TABLE aa_time_dim;
---------------------
set @i = -1;
set @sql = repeat(" select 1 union all",-datediff('2021-01-01','2030-12-31')+1);
set @sql = left(@sql,length(@sql)-length(" union all"));
set @sql = concat("select date_add('2021-01-01',interval @i:=@i+1 day) as date from (",@sql,") as tmp");
prepare stmt from @sql;
execute stmt
---------------------
作者:longvs
来源:CSDN
原文:https://blog.csdn.net/longvs/article/details/22745433
版权声明:本文为博主原创文章,转载请附上博文链接!