Re: Recurring events

Francesco Casadei <f_casadei@libero.it> writes:
> Thank you. Actually I have already considered the solution you suggested,
> but as stated in the FAQ "Working with Dates and Times in PostgreSQL":

> Because DATE differences are always calculated as whole numbers of days,
> DATE/INTEGER cannot figure out the varying lengths of months and years. Thus,
> you cannot use DATE/INTEGER to schedule something for the 5th of every month
> without some very fancy length-of-month calculating on the fly.

> After reading this I thought that the DATE/INTEGER solution wasn't the right
> solution.

Good point, but your original question was founded on the assumption
that you wanted events to recur every so many days; otherwise the entire
concept of computing number-of-days modulo a recurrence length is bogus.

If you want to allow symbolic recurrence intervals like '1 month' then
I agree you need to use the timestamp/interval math to do the
calculation.  But I'm not sure there is any real easy way to determine
whether a given day is any of the (irregularly spaced) recurrences.
Certainly a modulo calculation will not work.

Possibly you could do it with a loop in a plpgsql function.  Something
along the lines of (just pseudocode):

function is_recurrence(startdate date, recurrence interval, target date)

    date    current := startdate;
    integer steps := 0;

    while (target > current)
    {
        steps := steps + 1;
        current := (startdate::timestamp + (recurrence * steps)) :: date;
    }

    if (target = current)
      return true;
    else
      return false;

Ugly, but I can't see any way to do better...

            regards, tom lane