mysql创建时间维度表

drop TABLE IF EXISTS dim_time;
CREATE TABLE dim_time
(time_key BIGINT not null COMMENT 'primary key',
time_id time COMMENT '%H:%i:%s',
hour_short SMALLINT COMMENT '0,1,2,3...23',
hour_short_f SMALLINT COMMENT '1,2,3...24',
hour_slot varchar(6) COMMENT '0-1,1-2,2-3,...,23-24',

hour_of_day varchar(5) COMMENT '00,01,02,03...23',
hour_of_day_l varchar(5) COMMENT '0,1,2,3...23',
minute_id varchar(5) COMMENT '0-59',
description varchar(255) COMMENT '备注',
PRIMARY KEY (`time_key`)  
)ENGINE=MyIsam DEFAULT CHARSET=utf8;

create procedure f_dim_time()
begin

    DECLARE date_time datetime;
    declare i int;
    set i=0;
    set date_time = STR_TO_DATE('2018-09-09','%Y-%m-%d %H:%i:%s');
    DELETE from dim_time;

    while i < 86400 DO
        INSERT into dim_time
            (time_key,
            time_id,
            hour_short,
            hour_short_f,
            hour_slot,
            hour_of_day,
            hour_of_day_l,
            minute_id,
            description)
        SELECT
            i time_key,
            TIME(date_time) time_id,
            FLOOR(i/3600) hour_short,
            FLOOR(i/3600)+1 hour_short_f,
            CONCAT(FLOOR(i/3600),'~',FLOOR(i/3600)+1) hour_slot,
            DATE_FORMAT(date_time,'%H') hour_of_day,
            DATE_FORMAT(date_time,'%k') hour_of_day_l,
            DATE_FORMAT(date_time,'%i') minute_id,

            '' description
        from dual;
        set i=i+1;
    set date_time = DATE_ADD('2018-01-01 00:00:00',INTERVAL i SECOND);
    end while;
end