/* Create calendar table for MySQL (Total runtime is less than 2 seconds to populate) */
-- DROP TABLE IF EXISTS calendar
CREATE TABLE calendar (
    CalendarDate DATE NOT NULL PRIMARY KEY,
    CalendarYear SMALLINT NULL,
    CalendarMonth tinyint NULL,
    CalendarDay tinyint NULL,
    CalendarMonthName VARCHAR(9) NULL,
    CalendarDayName VARCHAR(9) NULL,
    CalendarDayofWeek tinyint NULL,
    CalendarIsWeekday tinyint NULL,
    CalendarQuarter tinyint NULL
);

-- BEGIN: Populate calendardate field using high performance method
DROP TABLE IF EXISTS calendar_ints; -- Note, we can't use a temporary table because we are going to self join to it
CREATE TABLE IF NOT EXISTS calendar_ints ( i tinyint );
INSERT INTO calendar_ints VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

INSERT INTO calendar (calendardate)
SELECT DATE('2020-01-01') + INTERVAL a.i*10000 + b.i*1000 + c.i*100 + d.i*10 + e.i DAY
FROM calendar_ints a JOIN calendar_ints b JOIN calendar_ints c JOIN calendar_ints d JOIN calendar_ints e
WHERE (a.i*10000 + b.i*1000 + c.i*100 + d.i*10 + e.i) < (365.25 * 80) -- Populate till 2099-12-31 (365.25 days per year * 80 years) 
ORDER BY 1;

DROP TABLE IF EXISTS calendar_ints;
-- END: Populate calendardate field using high performance method

-- Now update other fields
UPDATE calendar
SET    CalendarYear = YEAR(CalendarDate), -- 2022 (Results for 2022-03-17)
       CalendarMonth = MONTH(CalendarDate), -- 3
       CalendarDay = dayofmonth(CalendarDate), -- 17
       CalendarMonthName = monthname(CalendarDate), -- March
       CalendarDayName = dayname(CalendarDate), -- Thursday
       CalendarDayofWeek = dayofweek(CalendarDate), -- 5
       CalendarIsWeekday = CASE WHEN dayofweek(CalendarDate) IN (1,7) THEN 0 ELSE 1 END, -- 1
       CalendarQuarter = quarter(CalendarDate); -- 1
    
-- select * from calendar order by 1

/*
First record from table:

CalendarDate CalendarYear CalendarMonth CalendarDay CalendarMonthName CalendarDayName CalendarDayofWeek CalendarIsWeekday CalendarQuarter
------------ ------------ ------------- ----------- ----------------- --------------- ----------------- ----------------- ---------------
2020-01-01   2020         1             1           January           Wednesday       4                 1                 1
*/