generate_time_dimension.sql

/* 
MariaDB [hoge]> call populate_timedim();
Query OK, 7200 rows affected (8.972 sec)

MariaDB [hoge]> select * from timedim limit 10;
+----+---------------------+
| id | ts                  |
+----+---------------------+
|  1 | 2020-08-27 08:00:00 |
|  2 | 2020-08-27 08:00:01 |
|  3 | 2020-08-27 08:00:02 |
|  4 | 2020-08-27 08:00:03 |
|  5 | 2020-08-27 08:00:04 |
|  6 | 2020-08-27 08:00:05 |
|  7 | 2020-08-27 08:00:06 |
|  8 | 2020-08-27 08:00:07 |
|  9 | 2020-08-27 08:00:08 |
| 10 | 2020-08-27 08:00:09 |
+----+---------------------+
10 rows in set (0.001 sec)
*/

DROP TABLE IF EXISTS timedim;
CREATE TABLE timedim (
    id INT NOT NULL auto_increment,
    ts datetime,
    PRIMARY KEY(id)
) ENGINE=InnoDB;
CREATE INDEX timedim_ts ON timedim (ts);

delimiter //

DROP PROCEDURE IF EXISTS populate_timedim;
CREATE PROCEDURE populate_timedim ()
BEGIN
    DECLARE v_datetime DATETIME;

    DELETE FROM timedim;

    SET v_datetime = '2020-08-27 08:00:00';
    WHILE v_datetime < '2020-08-27 10:00:00' DO
        INSERT INTO timedim ( ts ) VALUES ( v_datetime );
        SET v_datetime = DATE_ADD(v_datetime, INTERVAL 1 SECOND);
    END WHILE;
END;

//
delimiter ;