[MySQL] 创建间隔5分钟的时间表

旧城等待， 2022-09-26 11:56 148阅读 0赞

**间隔5分钟时间表**，为了与其他表做联合查询时解决空缺时间点问题

方法一：创建存储过程，一次性插入N年的数据，每笔数据间隔5分钟

创建表

create table tb_date (date_day datetime());

创建存储过程

delimiter $
    
    create procedure p1
    
    begin 
    declare i int;
    declare j int;
    declare time1 datetime;
    set i = 3000;
    set j = 1;
    set time1 = now();
    while i > 0
    do
    #insert into tb_minute_copy select now() - INTERVAL i*5 MINUTE;
    insert into tb_minute_copy select time1 - INTERVAL i*5 MINUTE;
    
    set i = i - 1;
    end while;
    while j < 5000000
    do
    insert into tb_minute_copy select time1 + INTERVAL j*5 MINUTE;
    set j = j + 1;
    end while;
    end $
    
    
    
    
    执行存储过程，写入数据
    
    call p1();

方法二：用定时器，设定5分钟整数倍分钟时执行，每5分钟执行一次向表中插入当前时间

定时器执行语句

insert into tb_minute select now()

**创建日历**

创建日历表

CREATE TABLE `dim_date` (  
    `date_id` int(11) NOT NULL COMMENT '20110512',  
    `date_name` varchar(16) DEFAULT NULL COMMENT '2011-05-12',  
    `date_of_month` int(11) DEFAULT NULL COMMENT '12',  
    `year_id` int(11) DEFAULT NULL COMMENT '2011',  
    `year_name` varchar(16) DEFAULT NULL COMMENT '2011年',  
    `quarter_id` int(11) DEFAULT NULL COMMENT '2',  
    `quarter_name` varchar(16) DEFAULT NULL COMMENT '2季度',  
    `month_id` int(11) DEFAULT NULL COMMENT '5',  
    `month_name` varchar(16) DEFAULT NULL COMMENT '5月',  
    `month_of_year_name` varchar(16) DEFAULT NULL COMMENT '2011年5月',  
    `month_of_year_id` int(11) DEFAULT NULL COMMENT '201105',  
    `week_id` int(11) DEFAULT NULL,  
    `week_name` varchar(16) DEFAULT NULL,  
    `week_of_year_id` int(11) DEFAULT NULL,  
    `week_of_year_name` varchar(32) DEFAULT NULL,  
    `is_weekend` enum('否','是') DEFAULT NULL COMMENT '是否周末',  
    PRIMARY KEY (`date_id`),  
    KEY `ix_dim_date_date_name` (`date_name`),  
    KEY `ix_dim_date_month_id` (`month_id`),  
    KEY `ix_dim_date_year_id` (`year_id`),  
    KEY `ix_dim_date_quanter_id` (`quarter_id`),  
    KEY `ix_dim_date_week_of_year_id` (`week_of_year_id`,`week_of_year_name`)  
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1  
    
    
    创建存储过程
    DROP PROCEDURE IF EXISTS pro_dim_date;  
    CREATE PROCEDURE pro_dim_date(IN bdate DATE,IN edate DATE)  
    BEGIN  
    DECLARE var DATE DEFAULT bdate;  
    DECLARE evar DATE DEFAULT DATE_ADD(edate,INTERVAL 1 DAY);  
    DECLARE bweek DATE;  
    DECLARE eweek DATE;  
    WHILE var<evar DO  
    SET bweek = DATE_ADD(DATE_SUB(var,INTERVAL 1 WEEK),INTERVAL 1 DAY);  
    SET eweek = DATE_SUB(DATE_ADD(var,INTERVAL 1 WEEK),INTERVAL 1 DAY);  
    INSERT INTO gyyx_report.dim_date   
    (  
    `date_id`,  
    `date_name`,  
    `date_of_month`,  
    `year_id`,  
    `year_name`,  
    `quarter_id`,  
    `quarter_name`,  
    `month_id`,  
    `month_name`,  
    `month_of_year_name`,  
    `month_of_year_id`,  
    `week_id`,  
    `week_name`,  
    `week_of_year_id`,  
    `week_of_year_name`,  
    `is_weekend`  
    )  
    VALUES  
    (  
    DATE_FORMAT(var,'%Y%m%d'),  
    DATE_FORMAT(var,'%Y-%m-%d'),  
    DAYOFMONTH(var),  
    YEAR(var),  
    CONCAT(YEAR(var),'年'),  
    QUARTER(var),  
    CONCAT(QUARTER(var),'季度'),  
    DATE_FORMAT(var,'%Y%m'),  
    CONCAT(YEAR(var),'年',MONTH(var),'月'),  
    CONCAT(MONTH(var),'月'),  
    MONTH(var),  
    WEEKDAY(var),  
    CASE WEEKDAY(var) WHEN 0 THEN '星期一' WHEN 1 THEN '星期二' WHEN 2 THEN '星期三' WHEN 3 THEN '星期四' WHEN 4 THEN '星期五' WHEN 5 THEN '星期六' WHEN 6 THEN '星期日' END, 
    WEEKOFYEAR(var),  
    CONCAT('第',WEEKOFYEAR(var),'周(',MONTH(bweek),'月',DAY(bweek),'日~',MONTH(eweek),'月',DAY(eweek),'日'), 
    CASE WHEN WEEKDAY(var)>4 THEN '是' ELSE '否' END   
    );  
    SET var=DATE_ADD(var,INTERVAL 1 DAY);  
    END WHILE;  
    END

调用存储过程，填入日历表

call p7(NOW()-INTERVAL 31 DAY, NOW() + INTERVAL 10000 DAY)