From: | Francesco Casadei <f_casadei(at)libero(dot)it> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Recurring events |
Date: | 2002-01-26 16:27:41 |
Message-ID: | 20020126172741.A2169@junior.kasby |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2002-01-26 17:04:14 | Re: Recurring events |
Previous Message | Joerg Hessdoerfer | 2002-01-26 15:49:42 | Re: Problems with initdb on Cygwin |