Data Warehouse for AD Dates

Question

We are creating a historical archive for the world history database, and we need a date search table that lists all the dates in AD. How to make the creation of values ​​for this table - from 1AD to 2011 as YYYY / MM / DD? The database is MySQL.

Problems:

• I use Excel to pre-populate dates, and then import into MySQL as: YYYY / MM / DD, but Excel does not recognize years such as 0007, 0008, etc., so I cannot autocopy to generate dates. I have to do it manually, and it will take several days to switch from 1AD to 2011 in the format YYYY / MM / DD.

• Linear years were introduced in 1752. If I programmatically generate dates, how do I handle cases before 1752 without leap years? This will result in incorrect dates.

My table:

CREATE TABLE `dates` ( `date_id` int(10) NOT NULL, `format` char(10) NOT NULL, `century` int(10) NOT NULL, `decade` int(10) NOT NULL, `year` int(10) NOT NULL, `month` int(10) NOT NULL, `week` int(10) NOT NULL, `day` int(10) NOT NULL, `month_year` int(10) NOT NULL, `week_year` int(10) NOT NULL, `week_month` int(10) NOT NULL, `day_year` int(10) NOT NULL, `day_month` int(10) NOT NULL, `day_week` int(10) NOT NULL, PRIMARY KEY (`date_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 

Answer 1

Use something like this SQL (using my own table structure, not the one you had):

 DROP TABLE IF EXISTS time_dimension; CREATE TABLE time_dimension ( id INTEGER PRIMARY KEY, -- year*10000+month*100+day db_date DATE NOT NULL, year INTEGER NOT NULL, month INTEGER NOT NULL, -- 1 to 12 day INTEGER NOT NULL, -- 1 to 31 quarter INTEGER NOT NULL, -- 1 to 4 week INTEGER NOT NULL, -- 1 to 52/53 day_name VARCHAR(9) NOT NULL, -- 'Monday', 'Tuesday'... month_name VARCHAR(9) NOT NULL, -- 'January', 'February'... holiday_flag CHAR(1) DEFAULT 'f' CHECK (holiday_flag in ('t', 'f')), weekend_flag CHAR(1) DEFAULT 'f' CHECK (weekday_flag in ('t', 'f')), event VARCHAR(50), UNIQUE td_ymd_idx (year,month,day), UNIQUE td_dbdate_idx (db_date) ) Engine=MyISAM; DROP PROCEDURE IF EXISTS fill_date_dimension; DELIMITER // CREATE PROCEDURE fill_date_dimension(IN startdate DATE,IN stopdate DATE) BEGIN DECLARE currentdate DATE; SET currentdate = startdate; WHILE currentdate < stopdate DO INSERT INTO time_dimension VALUES ( YEAR(currentdate)*10000+MONTH(currentdate)*100 + DAY(currentdate), currentdate, YEAR(currentdate), MONTH(currentdate), DAY(currentdate), QUARTER(currentdate), WEEKOFYEAR(currentdate), DATE_FORMAT(currentdate,'%W'), DATE_FORMAT(currentdate,'%M'), 'f', CASE DAYOFWEEK(currentdate) WHEN 1 THEN 't' WHEN 7 then 't' ELSE 'f' END, NULL); SET currentdate = ADDDATE(currentdate,INTERVAL 1 DAY); END WHILE; END // DELIMITER ; TRUNCATE TABLE time_dimension; CALL fill_date_dimension('1-01-01','2015-01-01'); OPTIMIZE TABLE time_dimension;