DELIMITER $$ CREATE PROCEDURE `stock`.`gen_time_sequence`(startstamp TIMESTAMP, endstamp TIMESTAMP, intval INTEGER, unitval VARCHAR(10)) BEGIN DECLARE thisStamp TIMESTAMP; DECLARE nextStamp TIMESTAMP; SET thisStamp = startstamp; -- Drop and Create the temp table DROP TEMPORARY TABLE IF EXISTS timestamp_sequence; CREATE TEMPORARY TABLE IF NOT EXISTS timestamp_sequence( id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, TIME TIMESTAMP ); -- 迴圈, 每次增加間隔 REPEAT SELECT CASE unitval WHEN 'MICROSECOND' THEN TIMESTAMPADD(MICROSECOND, intval, thisStamp) WHEN 'SECOND' THEN TIMESTAMPADD(SECOND, intval, thisStamp) WHEN 'MINUTE' THEN TIMESTAMPADD(MINUTE, intval, thisStamp) WHEN 'HOUR' THEN TIMESTAMPADD(HOUR, intval, thisStamp) WHEN 'DAY' THEN TIMESTAMPADD(DAY, intval, thisStamp) WHEN 'WEEK' THEN TIMESTAMPADD(WEEK, intval, thisStamp) WHEN 'MONTH' THEN TIMESTAMPADD(MONTH, intval, thisStamp) WHEN 'QUARTER' THEN TIMESTAMPADD(QUARTER, intval, thisStamp) WHEN 'YEAR' THEN TIMESTAMPADD(YEAR, intval, thisStamp) END INTO nextStamp; -- 過濾週六,週日與特殊日子 IF DAYOFWEEK(thisStamp) <> 1 AND DAYOFWEEK(thisStamp) <> 7 OR DATE(thisStamp) = STR_TO_DATE('2012-08-02', '%Y-%m-%d') THEN INSERT INTO timestamp_sequence(Dateid, Weekdayid) SELECT DATE(thisStamp), DAYOFWEEK(thisStamp); ELSEIF DATE(thisStamp) = STR_TO_DATE('2012-03-03', '%Y-%m-%d') THEN INSERT INTO timestamp_sequence(Dateid, Weekdayid) SELECT DATE(thisStamp), DAYOFWEEK(thisStamp); END IF; SET thisStamp = nextStamp; UNTIL thisStamp >= endstamp END REPEAT; END$$ DELIMITER ;
要產生時間序列前先初始化gen_time_sequence預存程序
CALL gen_time_sequence('2012-01-01','2012-12-31', 1, 'DAY');
取得時間序列產生的天數,執行以下SQL指令
SELECT COUNT(1) FROM timestamp_sequence;
執行結果
取得時間序列中的前10筆資料
select * from timestamp_sequence limit 10;
執行結果
沒有留言:
張貼留言