Calendar table for MySql

create_calendar_table.sql

SET FOREIGN_KEY_CHECKS = 0;
/* Drop Tables */

DROP TABLE IF EXISTS calendar CASCADE;

/* Create Tables */

CREATE TABLE calendar (
  id               DATE       NOT NULL,
  year                  SMALLINT   NOT NULL,
  month                 TINYINT    NOT NULL, -- 1 to 12
  day                   TINYINT    NOT NULL, -- 1 to 31
  quarter               TINYINT    NOT NULL, -- 1 to 4
  week                  TINYINT    NOT NULL, -- 1 to 52/53
  day_name              VARCHAR(9) NOT NULL, -- 'Monday', 'Tuesday'...
  month_name            VARCHAR(9) NOT NULL, -- 'January', 'February'...
  day_of_week           TINYINT    NOT NULL, -- Day number in week 1 to 7
  day_of_quarter        TINYINT    NOT NULL, -- The day number within the current quarter
  day_of_year           SMALLINT   NOT NULL, -- The day number out of the current year
  week_of_month         TINYINT    NOT NULL, -- The week number within the current month. With this calculation, the weeks count starting on the first of the month, regardless of the day of week.
  week_of_quarter       TINYINT    NOT NULL, -- The week number within the current quarter
  year_week             MEDIUMINT  NOT NULL, -- The year and week number as INT, ex. 202012 is week 12 of year 2020
  first_date_of_week    DATE       NOT NULL, -- first date of current week: Monday
  last_date_of_week     DATE       NOT NULL, -- last date of current week: Sunday
  first_date_of_month   DATE       NOT NULL, -- first date of current month
  last_date_of_month    DATE       NOT NULL, -- last date of current month
  first_date_of_quarter DATE       NOT NULL, -- first date of current quarter
  last_date_of_quarter  DATE       NOT NULL, -- last date of current quarter
  first_date_of_year    DATE       NOT NULL, -- first date of current year
  last_date_of_year     DATE       NOT NULL, -- last date of current year
  is_weekday            BIT        NOT NULL DEFAULT 0, -- true if the date is a weekday: Monday-Friday
  is_leap_year          BIT        NOT NULL DEFAULT 0, -- indicates that current year is leap or not
  days_in_month         TINYINT    NOT NULL, -- how many days are in current month
  
  CONSTRAINT `PK_calendar` PRIMARY KEY (`id` ASC)

)
  Engine = INNODB;


/* Create Primary Keys, Indexes, Uniques, Checks */

ALTER TABLE `calendar`
  ADD UNIQUE INDEX `IXUQ_calendar_ymd` (`year`, `month`, `day`);

SET FOREIGN_KEY_CHECKS = 1;


/*  Create procedures and functions */

DROP PROCEDURE IF EXISTS fill_calendar;

DELIMITER //
CREATE PROCEDURE fill_calendar(IN startdate DATE, IN stopdate DATE)
  BEGIN

    DECLARE currentdate DATE;

    DECLARE first_day_of_week DATE;
    DECLARE last_day_of_week DATE;
    DECLARE first_day_of_month DATE;
    DECLARE last_day_of_month DATE;
    DECLARE first_day_of_quarter DATE;
    DECLARE last_day_of_quarter DATE;
    DECLARE first_day_of_year DATE;
    DECLARE last_day_of_year DATE;

    DECLARE num_day_of_quarter TINYINT;
    DECLARE num_week_of_month TINYINT;
    DECLARE num_week_of_quarter TINYINT;
    DECLARE num_days_in_month TINYINT;

    DECLARE leap_year BIT;
    DECLARE weekday BIT;

    SET currentdate = startdate;

    WHILE currentdate < stopdate DO

      SET first_day_of_week = SUBDATE(currentdate, WEEKDAY(currentdate));
      SET last_day_of_week = DATE(currentdate + INTERVAL (6 - WEEKDAY(currentdate)) DAY );
      SET first_day_of_month = DATE_ADD(currentdate, INTERVAL - DAY(currentdate) +1 DAY );
      SET last_day_of_month = LAST_DAY(currentdate);
      SET first_day_of_quarter = MAKEDATE(YEAR(currentdate), 1) + INTERVAL QUARTER(currentdate) QUARTER - INTERVAL    1 QUARTER;
      SET last_day_of_quarter = MAKEDATE(YEAR(currentdate),1) + INTERVAL QUARTER(currentdate) QUARTER - INTERVAL 1 DAY;
      SET first_day_of_year = MAKEDATE(YEAR(currentdate),1);
      SET last_day_of_year = MAKEDATE(YEAR(currentdate) +1 ,1) - INTERVAL  1 DAY;

      SET num_day_of_quarter = DATEDIFF(currentdate, first_day_of_quarter) +1;
      SET num_week_of_month = WEEK(currentdate) - WEEK(first_day_of_month) +1;
      SET num_week_of_quarter = WEEK(currentdate) - WEEK(first_day_of_quarter) +1;
      SET num_days_in_month = DAY(last_day_of_month);

      SET leap_year = CASE WHEN YEAR(currentdate) % 4 = 0 THEN 1 ELSE 0 END;

      SET weekday = CASE WHEN DAYOFWEEK(currentdate) IN (1,7) THEN 0 ELSE 1 END;

      INSERT INTO calendar (id,
                            year,
                            month,
                            day,
                            quarter,
                            week,
                            day_name,
                            month_name,
                            day_of_week,
                            day_of_quarter,
                            day_of_year,
                            week_of_month,
                            week_of_quarter,
                            year_week,
                            first_date_of_week,
                            last_date_of_week,
                            first_date_of_month,
                            last_date_of_month,
                            first_date_of_quarter,
                            last_date_of_quarter,
                            first_date_of_year,
                            last_date_of_year,
                            is_leap_year,
                            is_weekday,
                            days_in_month)
      VALUES (
              currentdate,
              YEAR(currentdate),
              MONTH(currentdate),
              DAY(currentdate),
              QUARTER(currentdate),
              WEEKOFYEAR(currentdate),
              DATE_FORMAT(currentdate, '%W'),
              DATE_FORMAT(currentdate, '%M'),
              WEEKDAY(currentdate) +1,
              num_day_of_quarter,
              DAYOFYEAR(currentdate),
              num_week_of_month,
              num_week_of_quarter,
              YEARWEEK(currentdate,3),
              first_day_of_week,
              last_day_of_week,
              first_day_of_month,
              last_day_of_month,
              first_day_of_quarter,
              last_day_of_quarter,
              first_day_of_year,
              last_day_of_year,
              leap_year,
              weekday,
              num_days_in_month
                 );
      SET currentdate = ADDDATE(currentdate, INTERVAL 1 DAY);
    END WHILE;
  END
//
DELIMITER ;

CALL fill_calendar('2000-01-01', '2100-01-01');
OPTIMIZE TABLE calendar;