Re: Recurring events - Mailing list pgsql-general

Le mardi 07 juin 2011 à 13:28 +0200, Thomas Guettler a écrit :
>
> On 07.06.2011 09:57, Vincent Veyron wrote:
> > Le lundi 06 juin 2011 à 12:59 +0200, Thomas Guettler a écrit :
> >
> >> how do you store recurring events in a database?
> >>
> >> Selecting all events in a week/month should be fast (comming from an index).
> >>
> >> My solution looks like this:
> >>
> >> Table event:
> >>
> >> Columns: id, name, recurring, start_datetime, end_datetime
> >>
> >> recurring is weekly, monthly, yearly or NULL.
> >>
> >
> > Maybe you could try something like what is used in cron, the scheduling
> > program for GNU/Linux
>
> I know cron very well. But I need to get all events on day X between time1 and time2 very quickly.
> If I build a crontab like table, I need to check all entries before I can
> know which crontab lines get executed during this period.
>

Well, this would require some thoughts and a study of the data, but the
idea is to do :


CREATE TABLE event (
id serial primary key,
name text,
dow integer,
dom integer,
h_start time);

insert into event (name, dow, h_start)
values ('event1', 1, '09:45');

insert into event (name, dow, h_start)
values ('event2', 2, '09:45');

select * from event where dow=(
SELECT EXTRACT(DOW FROM current_date));


 id |  name  | dow | dom | h_start
----+--------+-----+-----+----------
  2 | event2 |   2 |     | 09:45:00

You would have to build the proper indexes. There are many possible
variations for the model, depending on what your data is like, how it is
generated, etc...

Another possibility, which might or might not apply, is to use a
structure inspired from a subscription database :

CREATE TABLE event_2 (
id serial primary key,
name text,
last_event_date timestamp,
delay_before_next_occurence interval);

insert into event_2 (name, last_event_date,
delay_before_next_occurence)
values ('event1', current_date - interval '1 day', '1 day');

insert into event_2 (name, last_event_date,
delay_before_next_occurence)
values ('event1', current_date - interval '2 days', '1 day');

select * from event_2 where last_event_date +
delay_before_next_occurence=current_date;

 id |  name  |   last_event_date   | delay_before_next_occurence
----+--------+---------------------+-----------------------------
  1 | event1 | 2011-06-06 00:00:00 | 1 day

A lot depends on the data and its distribution, which requires quite a
bit of study. I find it pays off rather well in application development
time afterwards, though.



--
Vincent Veyron
http://marica.fr/
Logiciel de gestion des sinistres et des contentieux pour le service juridique