sql 语句生成指定时间列表

业务需求：

需要一张时间列表，格式包含年月日周，零点时间戳，格式化时间，季度【过程中未涉及到，可自行判断设置】

创建表结构：

DROP TABLE IF EXISTS `obj_date`;
CREATE TABLE `obj_date` (
  `id` int(8) NOT NULL AUTO_INCREMENT,
  `datetime` int(10) NOT NULL COMMENT '时间戳',
  `format` date NOT NULL COMMENT '格式化时间2015-01-01',
  `year` int(8) DEFAULT NULL COMMENT '年',
  `month` int(8) DEFAULT NULL COMMENT '月',
  `day` int(8) DEFAULT NULL COMMENT '天',
  `week` int(8) DEFAULT NULL COMMENT '周',
  `quater` int(2) DEFAULT NULL COMMENT '季度',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

创建存储过程

DROP PROCEDURE IF EXISTS getAllDate;
CREATE PROCEDURE getAllDate()  
BEGIN  
    DECLARE count INT DEFAULT 0;  
    DECLARE startDay DATE DEFAULT DATE('2015-01-01 00:00:00');  
    WHILE count<3650 DO  
        INSERT INTO obj_date(datetime,format,year,month,day,week) VALUES (unix_timestamp(startDay),startDay,YEAR(startDay),MONTH(startDay),DAY(startDay),WEEKOFYEAR(startDay));  
        SET count=count+1;  
        SET startDay=DATE_ADD(DATE('2015-01-01 00:00:00'),INTERVAL count DAY);   
    END WHILE;  
END

增加季度

CREATE PROCEDURE getAllDate() BEGIN DECLARE count INT DEFAULT 0;
  DECLARE
  startDay DATE DEFAULT DATE( '2015-01-01 00:00:00' );
  WHILE
  count < 3650 DO
    INSERT INTO datedict ( time, format, YEAR, MONTH, DAY, WEEK,quarter )
    VALUES
      (
        unix_timestamp( startDay ),
        startDay,
        YEAR ( startDay ),
        MONTH ( startDay ),
        DAY ( startDay ),
        WEEKOFYEAR( startDay ),
        case  when
          FIND_IN_SET(DATE_FORMAT(DATE( startDay ), '%m'),'03,04,05') <> 0 -- 不等于0是，月份在'3,4,5'中
          then '春'
        when
          FIND_IN_SET(DATE_FORMAT(DATE( startDay ), '%m'),'06,07,08') <> 0
          then '夏'
        when
          FIND_IN_SET(DATE_FORMAT(DATE( startDay ), '%m'),'09,10,11') <> 0
          then '秋'
        when
          FIND_IN_SET(DATE_FORMAT(DATE( startDay ), '%m'),'12,01,02') <> 0
          then '冬'
        end);
    SET count = count + 1;

    SET startDay = DATE_ADD( DATE( '2015-01-01 00:00:00' ), INTERVAL count DAY );

  END WHILE;

END;

 

增强版来啦，增加年月字段，年季字段

CREATE DEFINER=`root`@`localhost` PROCEDURE `getAllDate`()
BEGIN DECLARE count INT DEFAULT 0;
  DECLARE
  startDay DATE DEFAULT DATE( '2015-01-01 00:00:00' );
  WHILE
  count < 3650 DO
    INSERT INTO datedict ( time, format, YEAR, MONTH, DAY, WEEK,quarter,yearmonth,yearquarter )
    VALUES
      (
        unix_timestamp( startDay ),
        startDay,
        YEAR ( startDay ),
        MONTH ( startDay ),
        DAY ( startDay ),
        WEEKOFYEAR( startDay ),
        case  when
          FIND_IN_SET(DATE_FORMAT(DATE( startDay ), '%m'),'03,04,05') <> 0 -- 不等于0是，月份在'3,4,5'中
          then '春'
        when
          FIND_IN_SET(DATE_FORMAT(DATE( startDay ), '%m'),'06,07,08') <> 0
          then '夏'
        when
          FIND_IN_SET(DATE_FORMAT(DATE( startDay ), '%m'),'09,10,11') <> 0
          then '秋'
        when
          FIND_IN_SET(DATE_FORMAT(DATE( startDay ), '%m'),'12,01,02') <> 0
          then '冬'
        end,
        CONCAT(YEAR ( startDay ),'-',MONTH ( startDay )),
        CONCAT(YEAR ( startDay ),'-',case  when
          FIND_IN_SET(DATE_FORMAT(DATE( startDay ), '%m'),'03,04,05') <> 0 -- 不等于0是，月份在'3,4,5'中
          then '春'
                                     when
                                       FIND_IN_SET(DATE_FORMAT(DATE( startDay ), '%m'),'06,07,08') <> 0
                                       then '夏'
                                     when
                                       FIND_IN_SET(DATE_FORMAT(DATE( startDay ), '%m'),'09,10,11') <> 0
                                       then '秋'
                                     when
                                       FIND_IN_SET(DATE_FORMAT(DATE( startDay ), '%m'),'12,01,02') <> 0
                                       then '冬'
                                     end)
      );
    SET count = count + 1;

    SET startDay = DATE_ADD( DATE( '2015-01-01 00:00:00' ), INTERVAL count DAY );

  END WHILE;

END

 

表结构也上了

CREATE TABLE `datedict`  (
  `id` int(10) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `time` varchar(15) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '时间戳',
  `format` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '格式化时间(2018-01-01 01:00:00)',
  `week` int(2) NOT NULL COMMENT '周',
  `quarter` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '季度',
  `day` int(2) NOT NULL COMMENT '天数',
  `month` int(2) NOT NULL COMMENT '月份',
  `year` varchar(4) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '年份',
  `season` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '季节',
  `yearmonth` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '年月',
  `yearquarter` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '年季',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3651 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

 

 

运行存储过程

CALL getAllDate();  

 

收工！

参考网址：https://blog.csdn.net/qq_36663951/article/details/78211208