Skip to content

Instantly share code, notes, and snippets.

@dgreenway

dgreenway/calendar.sql

Last active August 24, 2021 20:18
Show Gist options
  • Save dgreenway/89370e0bba06655afaf786da7c8e5730 to your computer and use it in GitHub Desktop.
Save dgreenway/89370e0bba06655afaf786da7c8e5730 to your computer and use it in GitHub Desktop.
Download ZIP
MySQL Calendar Table #sql #mysql
Raw
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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment