From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Francesco Casadei <f_casadei(at)libero(dot)it> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Recurring events |
Date: | 2002-01-26 20:24:04 |
Message-ID: | 17058.1012076644@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Marc G. Fournier | 2002-01-27 05:49:28 | PostgreSQL v7.2rc2 Released |
Previous Message | Francesco Casadei | 2002-01-26 19:30:58 | Re: Recurring events |