AD日期的数据仓库 [英] Data warehouse for AD dates
问题描述
我们正在为世界历史数据库创建历史档案,我们需要一个引用AD中所有日期的日期查询表。如何创建这个表的值 - 从1AD到2011为YYYY / MM / DD?数据库是MySQL。
We're creating a historic archive for a world history database and we need a date lookup table which references all dates in AD. How to go about creating the values for this table - from 1AD to 2011 as YYYY/MM/DD? Database is MySQL.
问题:
-
我正在使用Excel预先填充日期,然后导入MySQL为:YYYY / MM / DD,但Excel不能识别像0007,0008等的年份,所以我无法自动复制单元格以生成日期。我必须手动执行,这将需要几天的时间从1AD到2011年为YYYY / MM / DD。
I'm using Excel to pre-populate the dates, then import into MySQL as: YYYY/MM/DD but Excel doesn't recognize years like 0007, 0008, etc so I can't auto-copy cells to generate dates. I have to manually do it and this will take days to go from 1AD to year 2011 as YYYY/MM/DD.
闰年是在1752年推出的。如果我以编程方式生成日期,如何在1752年之前处理没有闰年的案件?这将导致错误的日期。
Leap years were introduced on 1752. If I programmatically generates dates how do I handle cases prior to 1752 with no leap years? It will generate wrong dates.
我的表:
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;
推荐答案
使用像这样的SQL(使用我自己的表结构,而不是你所拥有的那个):
Use something like this SQL (using my own table structure, not the one you had though):
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;
这篇关于AD日期的数据仓库的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!