1)mysql表:t_insert,表中字段:time_key(日期) fulldate_key(不同格式日期) calendar_quarter(该日期属于一年中第几个季节)2)要求:
假如输入20110101日期调用存储过程,实现效果如下:
time_key fulldate_key calendar_quarter
20110101 2011-01-01 00:00:00 4
20110102 2011-01-02 00:00:00 4
20110103 2011-01-03 00:00:00 4
20110301 2011-03-01 00:00:00 1
20110302 2011-03-02 00:00:00 1
如此类推大家帮帮忙。。
假如输入20110101日期调用存储过程,实现效果如下:
time_key fulldate_key calendar_quarter
20110101 2011-01-01 00:00:00 4
20110102 2011-01-02 00:00:00 4
20110103 2011-01-03 00:00:00 4
20110301 2011-03-01 00:00:00 1
20110302 2011-03-02 00:00:00 1
如此类推大家帮帮忙。。
values ('输入的时间',TIMESTAMP('输入的时间'),QUARTER('输入的时间'));
我只是打个比方.输入的值可以用自己生成啊,用while do 或则repeat 循环 就可以了
BEGIN
DECLARE v_startTime DATETIME;
DECLARE v_endTime DATETIME;
SELECT STR_TO_DATE(startTimeStr, '%Y%m%d') INTO v_startTime;
SELECT STR_TO_DATE(endTimeStr, '%Y%m%d') INTO v_endTime;
WHILE v_startTime <v_endTime DO
INSERT INTO t_dim_time1(time_key,fulldate_key,calendar_quarter,calendar_year,calendar_semester,day_numberof_week,day_numberof_month,
day_numberof_year,month_numberof_year,year_month1,year_month_text,year_month_day_text)
VALUES
(SUBSTR(DATE_FORMAT(v_startTime,'%Y%m%d'),1,8),DATE_ADD(v_startTime, INTERVAL 0 HOUR),
QUARTER(v_startTime),YEAR(v_startTime ),IF(MONTH(v_startTime)<=6,1,2),DAYOFWEEK(v_startTime),DAYOFMONTH(v_startTime),DAYOFYEAR(v_startTime),
MONTH(v_startTime),EXTRACT(YEAR_MONTH FROM v_startTime),DATE_FORMAT(v_startTime,'%y年%m月'),SUBSTR(v_startTime,6,5));
SET v_startTime= DATE_ADD(v_startTime,INTERVAL 1 DAY) ;
-- SET startTimeStr= startTimeStr+1 ;
END WHILE;
END$$DELIMITER ;
CREATE TABLE `t_dim_time` (
`time_key` decimal(22,0) DEFAULT NULL,
`fulldate_key` datetime DEFAULT NULL,
`calendar_quarter` decimal(22,0) DEFAULT NULL,
`calendar_year` decimal(22,0) DEFAULT NULL,
`calendar_semester` decimal(22,0) DEFAULT NULL,
`day_numberof_week` decimal(22,0) DEFAULT NULL,
`day_numberof_month` decimal(22,0) DEFAULT NULL,
`day_numberof_year` decimal(22,0) DEFAULT NULL,
`month_numberof_year` decimal(22,0) DEFAULT NULL,
`year_month1` decimal(22,0) DEFAULT NULL,
`year_month_text` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`year_month_day_text` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
KEY `idx_time_th` (`time_key`),
KEY `idx_time_ymd` (`year_month_day_text`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8