Re: Recurring events

Hi all,
I'm trying to store recurring events in a table using the TIMESTAMP/INTERVAL
combination:

CREATE TABLE events (
code INTEGER ,
name VARCHAR(255) NOT NULL,
start_date TIMESTAMP NOT NULL,
end_date TIMESTAMP NOT NULL,
recurrence INTERVAL NOT NULL,
description TEXT NOT NULL,

PRIMARY KEY (code)
);

In this way I should be able to describe events such "Wonderful party from
2002-01-26 to 2002-04-15 every 2 weeks" by inserting the following row:

INSERT INTO events VALUES (1, "Wonderful party", '2002-01-26'::timestamp,
'2002-04-15'::timestamp, '2 weeks'::interval', 'Don't miss this one!");

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()

where n is an integer number. Returning to the previos example if I visit the
'today events' section on February 9th, 2002 I should see the "Wonderful
party" description because:

'2002-01-26' + 1 * '2 weeks' = '2002-02-09'

What I need is calculate:

((now() - start_date) / recurrence)

and check that the result is an integer number (i.e. the interval
(now() - start_date) must be a multiple of recurrence).

The problem is that I cannot divide an interval by another interval (only by
a double precision number).

Another way is to calculate the number of days x between now()::date and
data_inizio::date (result is an integer), then convert recurrence from
interval to an integer i representing the number of days and check that x is a
multiple of i. Here the problem is I cannot cast type interval to int4.

Any ideas about managing recurring events?

Francesco Casadei

P.S. I already read the FAQ "Working with Dates and Times in PostgreSQL" at
http://techdocs.postgresql.org/techdocs/faqdatesintervals.php, searched the
archives and google, but I couldn't find anything helpful.

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

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

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