Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
MySQL: Generate Calendar Helper Table
DROP TABLE IF EXISTS `calendar`;
CREATE TABLE `calendar` (
`id` INTEGER PRIMARY KEY,
`date` DATE NOT NULL,
`year` INTEGER UNSIGNED NOT NULL,
`month` INTEGER UNSIGNED NOT NULL,
`day` INTEGER UNSIGNED NOT NULL,
`quarter` INTEGER UNSIGNED NOT NULL,
`week` INTEGER UNSIGNED NOT NULL,
`dayofweek` INTEGER UNSIGNED NOT NULL,
`weekday` INTEGER UNSIGNED NOT NULL,
`dayname` VARCHAR(9) NOT NULL,
`weekend` TINYINT(1) NOT NULL DEFAULT 0,
UNIQUE `td_ymd_idx` (`year`, `month`, `day`),
UNIQUE `td_dbdate_idx` (`date`)
) ENGINE=MYISAM;
DROP PROCEDURE IF EXISTS `fill_calendar`;
DELIMITER //
CREATE PROCEDURE `fill_calendar` (IN `startdate` DATE, IN `stopdate` DATE)
BEGIN
DECLARE `currentdate` DATE;
SET `currentdate` = `startdate`;
WHILE `currentdate` < `stopdate` DO
INSERT INTO `calendar` VALUES
(
YEAR(`currentdate`) * 10000 + MONTH(`currentdate`) * 100 + DAY(`currentdate`),
`currentdate`,
YEAR(`currentdate`),
MONTH(`currentdate`),
DAY(`currentdate`),
QUARTER(`currentdate`),
WEEKOFYEAR(`currentdate`),
DAYOFWEEK(`currentdate`),
WEEKDAY(`currentdate`),
DAYNAME(`currentdate`),
CASE DAYOFWEEK(`currentdate`) WHEN 1 THEN 1 WHEN 7 THEN 1 ELSE 0 END
);
SET `currentdate` = ADDDATE(`currentdate`, INTERVAL 1 DAY);
END WHILE;
END
//
DELIMITER ;
TRUNCATE TABLE `calendar`;
CALL fill_calendar('2000-01-01', '2040-01-01');
OPTIMIZE TABLE `calendar`;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.