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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
