Re: Storing recurrences in a SQL DBMS?

[Flavio S. Glock]

Dave Rolsky wrote:
On Tue, 3 May 2005, Flavio S. Glock wrote:
How about using a "view" to create a "lazy" sql recurrence.
For example:
 "FREQ=YEARLY;BYMONTH=3,6"
--- postgresql ---
CREATE TABLE YEARS ( N DATE UNIQUE PRIMARY KEY );
insert into years values ( date('1990-01-01') );
insert into years values ( date('1991-01-01') );
insert into years values ( date('1992-01-01') );
CREATE VIEW RECURR AS
   select ( n + interval '2 month' ) from years union
   select ( n + interval '5 month' ) from years;
----
The sql statements could be generated automatically using a 
DateTime::Format module.

That still won't handle "1st Sunday of the month" all that easily, as 
that's a more complex calculation.

It is not too difficult, actually:
CREATE VIEW MONTHS ( n ) AS
    select ( n + interval '2 month' ) from years union
    select ( n + interval '5 month' ) from years;
CREATE VIEW FIRST_WEEK_OF_MONTH ( n ) AS
    select ( n ) from MONTHS union
    select ( n + interval '1 day' ) from MONTHS union
    select ( n + interval '2 day' ) from MONTHS union
    select ( n + interval '3 day' ) from MONTHS union
    select ( n + interval '4 day' ) from MONTHS union
    select ( n + interval '5 day' ) from MONTHS union
    select ( n + interval '6 day' ) from MONTHS;
CREATE VIEW FIRST_SUNDAY AS
    select ( n ) from FIRST_WEEK_OF_MONTH
    where EXTRACT(DOW FROM n) = 0;
select * from FIRST_SUNDAY;
          n
---------------------
 1990-03-04 00:00:00
 1990-06-03 00:00:00
 1991-03-03 00:00:00
 1991-06-02 00:00:00
 1992-03-01 00:00:00
 1992-06-07 00:00:00
(6 registros)
- Flavio S. Glock