Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- -- Remember you need run this as two seperate statements. First delimiter, then the rest.
- DELIMITER //;
- CREATE PROCEDURE make_intervals(startdate TIMESTAMP, enddate TIMESTAMP, intval INTEGER, unitval VARCHAR(10))
- BEGIN
- -- *************************************************************************
- -- Procedure: make_intervals()
- -- Author: Ron Savage
- -- Date: 02/03/2009
- --
- -- Description:
- -- This procedure creates a temporary table named time_intervals with the
- -- interval_start and interval_end fields specifed from the startdate and
- -- enddate arguments, at intervals of intval (unitval) size.
- -- *************************************************************************
- DECLARE thisDate TIMESTAMP;
- DECLARE nextDate TIMESTAMP;
- SET thisDate = startdate;
- -- *************************************************************************
- -- Drop / create the temp table
- -- *************************************************************************
- DROP TEMPORARY TABLE IF EXISTS time_intervals;
- CREATE TEMPORARY TABLE IF NOT EXISTS time_intervals
- (
- interval_start TIMESTAMP,
- interval_end TIMESTAMP
- );
- -- *************************************************************************
- -- Loop through the startdate adding each intval interval until enddate
- -- *************************************************************************
- repeat
- SELECT
- CASE unitval
- WHEN 'MICROSECOND' THEN timestampadd(MICROSECOND, intval, thisDate)
- WHEN 'SECOND' THEN timestampadd(SECOND, intval, thisDate)
- WHEN 'MINUTE' THEN timestampadd(MINUTE, intval, thisDate)
- WHEN 'HOUR' THEN timestampadd(HOUR, intval, thisDate)
- WHEN 'DAY' THEN timestampadd(DAY, intval, thisDate)
- WHEN 'WEEK' THEN timestampadd(WEEK, intval, thisDate)
- WHEN 'MONTH' THEN timestampadd(MONTH, intval, thisDate)
- WHEN 'QUARTER' THEN timestampadd(QUARTER, intval, thisDate)
- WHEN 'YEAR' THEN timestampadd(YEAR, intval, thisDate)
- END INTO nextDate;
- INSERT INTO time_intervals SELECT thisDate, timestampadd(MICROSECOND, -1, nextDate);
- SET thisDate = nextDate;
- until thisDate >= enddate
- END repeat;
- END//
Add Comment
Please, Sign In to add comment