Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
MySQL Time Dimension generation SQL
-- credit to Akom's Tech Ruminations
-- http://tech.akom.net/archives/36-Creating-A-Basic-Date-Dimension-Table-in-MySQL.html
CREATE TABLE IF NOT EXISTS time_d (
time_id INT NOT NULL auto_increment,
fulltime time,
hour int,
minute int,
second int,
ampm varchar(2),
PRIMARY KEY(time_id)
) ENGINE=InnoDB AUTO_INCREMENT=1000;
delimiter //
DROP PROCEDURE IF EXISTS timedimbuild;
CREATE PROCEDURE timedimbuild ()
BEGIN
DECLARE v_full_date DATETIME;
DELETE FROM time_d;
SET v_full_date = '2009-03-01 00:00:00';
WHILE v_full_date < '2009-03-02 00:00:00' DO
INSERT INTO time_d (
fulltime ,
hour ,
minute ,
second ,
ampm
) VALUES (
TIME(v_full_date),
HOUR(v_full_date),
MINUTE(v_full_date),
SECOND(v_full_date),
DATE_FORMAT(v_full_date,'%p')
);
SET v_full_date = DATE_ADD(v_full_date, INTERVAL 1 SECOND);
END WHILE;
END;
//
delimiter ;
-- call the stored procedure
call timedimbuild();
@sunnycmf

This comment has been minimized.

Copy link
Owner Author

@sunnycmf sunnycmf commented Aug 12, 2014

for Date dimension generation, refer:
https://gist.github.com/sunnycmf/131a10a17d226e2ffb69

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment