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
