Re: Recurring events - Mailing list pgsql-general

From Vincent Veyron
Subject Re: Recurring events
Date
Msg-id 1307433467.2389.29.camel@asus-1001PX.home
Whole thread Raw
In response to Recurring events  (Thomas Guettler <hv@tbz-pariv.de>)
Responses Re: Recurring events
List pgsql-general
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

The crontab file looks like this

# m h dom mon dow user    command
17 *    * * *    root    run-parts --report /etc/cron.hourly
25 6    * * *    root    run-parts --report /etc/cron.daily )
47 6    * * 7    root    run-parts --report /etc/cron.weekly )
52 6    1 * *    root    run-parts --report /etc/cron.monthly )

m is minutes, h hours, dom day of month, mon month, dow day of week.
line 1 executes all scripts in /etc/cron.hourly at 17 minutes after each
hour; line 4 launches all scripts in /etc/cron.weekly every sunday at
06H47

using a similar structure for your table, you could then query for
events occuring weekly, monthly (by finding a value in the proper
column).

You would have to add fields such as first_event_date, last_event_date,
duration...


> end_datetime can be NULL (open end).
>

Then the event is not recurring, it just keeps going; I would consider
that a special case.


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


pgsql-general by date:

Previous
From: John R Pierce
Date:
Subject: Re: windows vista and windows 7
Next
From: wstrzalka
Date:
Subject: Estimate for 9.1 release