Re: Recurring events

Francesco Casadei <f_casadei(at)libero(dot)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