Generate date dimension table in MySQL

mysql-date-dimension.sql

-- Based on John Grimes gist: https://gist.github.com/johngrimes/408559
-- Choose the date range to generate by changing dates in WHERE clause at line 41

DROP TABLE IF EXISTS d_date;
CREATE TABLE d_date (
  id              BIGINT PRIMARY KEY, 
  `date`          DATE NOT NULL,
  `timestamp`     BIGINT NOT NULL, 
  day_name        CHAR(10) NOT NULL,
  is_weekday      BOOL NOT NULL,
  day             INT NOT NULL, 
  month           INT NOT NULL,
  year            INT NOT NULL,
  week_number     INT NOT NULL
);


INSERT INTO 
  d_date (id, `date`, `timestamp`, `day_name`, `is_weekday`, `day`, `month`, `year`, `week_number`)
SELECT
  DATE_FORMAT(`date`, '%Y%m%d'),
  `date`,
  UNIX_TIMESTAMP(`date`),
  DATE_FORMAT(`date`, '%W'),
  IF(DATE_FORMAT(`date`, '%w') IN (0, 6), FALSE, TRUE),
  DAY(`date`),
  MONTH(`date`),
  YEAR(`date`),
  DATE_FORMAT(`date`,'%v')
FROM 
  (SELECT * FROM 
    (
      SELECT 
        ADDDATE('1970-01-01', T4.I * 10000 + T3.I * 1000 + T2.I * 100 + T1.I * 10 + T0.I) AS `date` 
      FROM
        (SELECT 0 I UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS T0,
        (SELECT 0 I UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS T1,
        (SELECT 0 I UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS T2,
        (SELECT 0 I UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS T3,
        (SELECT 0 I UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS T4) AS dummy
    WHERE `date` BETWEEN '2017-01-01' AND '2029-01-01'
  ) AS source;