MySQL Calendar Table #sql #mysql

calendar.sql

CREATE TABLE `calendar_table` (
  `dt` date NOT NULL,
  `y` smallint(6) DEFAULT NULL,
  `m` tinyint(4) DEFAULT NULL,
  `d` tinyint(4) DEFAULT NULL,
  `dw` tinyint(4) DEFAULT NULL,
  `monthName` varchar(9) DEFAULT NULL,
  `dayName` varchar(9) DEFAULT NULL,
  `isWeekday` inary(1) DEFAULT NULL,
  `isHoliday` binary(1) DEFAULT NULL,
  `holidayDescr` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`dt`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE ints ( i tinyint );
 
INSERT INTO ints VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

-- Populate the table with 11322 dates from 2010-01-01 to 2040-12-31
INSERT INTO calendar_table (dt)
SELECT date('2012-01-01') + interval a.i*10000 + b.i*1000 + c.i*100 + d.i*10 + e.i day
FROM ints a JOIN ints b JOIN ints c JOIN ints d JOIN ints e
WHERE (a.i*10000 + b.i*1000 + c.i*100 + d.i*10 + e.i) <= 11322
ORDER BY 1;

-- update the other fields
UPDATE calendar_table
SET isWeekday = case when dayofweek(dt) IN (1,7) then 0 else 1 end,
	isHoliday = 0,
	y = year(dt),
	m = month(dt),
	d = dayofmonth(dt),
	dw = dayofweek(dt),
	monthname = monthname(dt),
	dayname = dayname(dt),
	holidayDescr = '';

DROP TABLE ints;