CmRq06ZF

1DELIMITER //
2CREATE PROCEDURE make_intervals(startdate timestamp, enddate timestamp, intval integer, unitval varchar(10))
3BEGIN
4-- *************************************************************************
5-- Procedure: make_intervals()
6--    Author: Ron Savage
7--      Date: 02/03/2009
8--
9-- Description:
10-- This procedure creates a temporary table named time_intervals with the
11-- interval_start and interval_end fields specifed from the startdate and
12-- enddate arguments, at intervals of intval (unitval) size.
13-- *************************************************************************
14   declare thisDate timestamp;
15   declare nextDate timestamp;
16   set thisDate = startdate;
17
18   -- *************************************************************************
19   -- Drop / create the temp table
20   -- *************************************************************************
21   drop temporary table if exists time_intervals;
22   create temporary table if not exists time_intervals
23      (
24      interval_start timestamp,
25      interval_end timestamp
26      );
27
28   -- *************************************************************************
29   -- Loop through the startdate adding each intval interval until enddate
30   -- *************************************************************************
31   repeat
32      select
33         case unitval
34            when 'MICROSECOND' then timestampadd(MICROSECOND, intval, thisDate)
35            when 'SECOND'      then timestampadd(SECOND, intval, thisDate)
36            when 'MINUTE'      then timestampadd(MINUTE, intval, thisDate)
37            when 'HOUR'        then timestampadd(HOUR, intval, thisDate)
38            when 'DAY'         then timestampadd(DAY, intval, thisDate)
39            when 'WEEK'        then timestampadd(WEEK, intval, thisDate)
40            when 'MONTH'       then timestampadd(MONTH, intval, thisDate)
41            when 'QUARTER'     then timestampadd(QUARTER, intval, thisDate)
42            when 'YEAR'        then timestampadd(YEAR, intval, thisDate)
43         end into nextDate;
44
45      insert into time_intervals select thisDate, timestampadd(MICROSECOND, -1, nextDate);
46      set thisDate = nextDate;
47   until thisDate >= enddate
48   end repeat;
49
50 END;
51 //