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.
