Re: Recurring events

On Sat, Jan 26, 2002 at 12:04:14PM -0500, Tom Lane wrote:
> Francesco Casadei <f_casadei(at)libero(dot)it> writes:
> > I want to publish these events on web and be able to show 'today events'. An
> > event happens today if:
>
> > start_date + n * recurrence = now()
>
> If you only want accuracy to the nearest day, I'd think you should be
> using type "date" not type "timestamp". Date subtraction gives
> integers:
>
> test71=# select current_date - '2002-01-20'::date;
> ?column?
> ----------
> 6
> (1 row)
>
> so making "recurrence" an integer too solves the problem.
>
> regards, tom lane
>
> end of the original message

Thank you. Actually I have already considered the solution you suggested,
but as stated in the FAQ "Working with Dates and Times in PostgreSQL":

[...]
However, TIMESTAMP is far better for real calendar calculations (e.g.
something that happens on the 15th of each month or the 2nd Thursday of leap
years).
[...]
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. Actually if I insert an event that happens on the 30th every month
from 2002-01-30 to 2002-05-30, then using the TIMESTAMP/INTERVAL solution:

=> SELECT '2002-01-30'::timestamp + '1 month'::interval;
?column?
------------------------
2002-02-28 00:00:00+01
(1 row)

the event should be displayed on 28th February, while with DATE/INTEGER would
be displayed on the 1st or 2nd March, depending by which value is used to
represent one month (30 or 31 days).

Francesco Casadei