Создать измерение времени в MySQL
Я пытаюсь сделать MySQL таблицу со следующими полями:
Год, месяц, день, час, минута
От 10 лет назад до 10 лет вперед.
У меня есть это, что создает его в течение нескольких дней, но это не включает часы и минуты
SET @d0 = "2012-01-01";
SET @d1 = "2012-12-31";
SET @date = date_sub(@d0, interval 1 day);
# set up the time dimension table
DROP TABLE IF EXISTS time_dimension;
CREATE TABLE `time_dimension` (
`date` date DEFAULT NULL,
`id` int NOT NULL,
`y` smallint DEFAULT NULL,
`m` smallint DEFAULT NULL,
`d` smallint DEFAULT NULL,
`yw` smallint DEFAULT NULL,
`w` smallint DEFAULT NULL,
`q` smallint DEFAULT NULL,
`wd` smallint DEFAULT NULL,
`m_name` char(10) DEFAULT NULL,
`wd_name` char(10) DEFAULT NULL,
PRIMARY KEY (`id`)
);
# populate the table with dates
INSERT INTO time_dimension
SELECT @date := date_add(@date, interval 1 day) as date,
# integer ID that allows immediate understanding
date_format(@date, "%Y%m%d") as id,
year(@date) as y,
month(@date) as m,
day(@date) as d,
date_format(@date, "%x") as yw,
week(@date, 3) as w,
quarter(@date) as q,
weekday(@date)+1 as wd,
monthname(@date) as m_name,
dayname(@date) as wd_name
FROM T
WHERE date_add(@date, interval 1 day) <= @d1
ORDER BY date
;
как я могу изменить это так, чтобы это также делало часы и минуты?
Спасибо!
Решение
Если вы выберете измерение времени, рекомендуется разделить измерения даты и времени, объединение обоих приведет к> 10 м записей для вашего измерения, а разделение измерений даты и времени даст вам 7300 строк для измерения даты и 1440 строк для Измерение времени (час / минута), после чего вам понадобится первичный ключ к измерению времени в таблице фактов так же, как вы проектируете его с измерением даты.
Другие решения
Вот что я использую
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')),
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
);
SET currentdate = ADDDATE(currentdate,INTERVAL 1 DAY);
END WHILE;
END
//
DELIMITER ;
TRUNCATE TABLE time_dimension;
CALL fill_date_dimension('1800-01-01','2050-01-01');
OPTIMIZE TABLE time_dimension;