mysql创建营业时间表_MySQL 创建临时时间表 存储过程

BEGIN

declare indexdate date; #当前时间，充当临时变量

set indexdate = sdate; #当前索引从初始化日期开

DROP TABLE IF EXISTS temp_datetable;

CREATE TABLE temp_datetable (

dateiso int(11) NOT NULL AUTO_INCREMENT COMMENT '日期序列号',

datekey date NOT NULL COMMENT '日期(格式：1978-12-25)',

quartername varchar(20) DEFAULT NULL COMMENT '季节',

monthofyear varchar(20) DEFAULT NULL COMMENT '年月(格式：19781225)',

monthname varchar(25) DEFAULT NULL COMMENT '月的名称(August)',

dayname varchar(25) DEFAULT NULL COMMENT '星期几(如：Friday)',

weekofyear int(11) DEFAULT NULL COMMENT '位于一年中的第几周',

dayofyear int(3) DEFAULT NULL COMMENT '一年中的第几天',

monthday_commvarchar(4)COMMENT '公历月日',

monthday_chnvarchar(8)COMMENT '农历月日',

year_commvarchar(4)COMMENT '公历年',

year_chnvarchar(4)COMMENT '农历年',

festival_commvarchar(100)COMMENT '公历节假日',

festival_chnvarchar(100)COMMENT '农历节假日',

festival_define varchar(100)COMMENT '自定义节假日',

inserttime TIMESTAMPCOMMENT '数据插入时间',

updatetime TIMESTAMP DEFAULT current_timestamp,

PRIMARY KEY (`dateiso`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

WHILE indexdate < edate DO

BEGIN

INSERT INTO temp_datetable

(

datekey

,quartername

,monthofyear

,monthname

,dayname

,weekofyear

,dayofyear

,inserttime

)

SELECT indexdate

,extract(quarter from indexdate)

,extract(year_month from indexdate)

,monthname(indexdate)

,dayname(indexdate)

,weekofyear(indexdate)

,dayofyear(indexdate)

,NOW();

SET indexdate = date_add(indexdate, interval 1 day);

END;

END WHILE;

END