Hi Craig and mailing list On 07.06.2011 00:54, Craig Ringer wrote: > On 06/06/2011 06:59 PM, Thomas Guettler wrote: >> Hi, >> >> how do you store recurring events in a database? > > I use two tables: one table that stores the recurring event, and another > that's essentially a materialized view containing instances of the event. > > It's not ideal, but performs better than using generate_series to > produce and filter the event series on the fly. > >> end_datetime can be NULL (open end). > > PostgreSQL has an ideal solution to this: the timestamp value > 'infinite'. Using it dramatically simplified my interval-related > queries. I initially used it in my design, only to discover that JDBC > doesn't support infinite dates (argh!) and neither do many languages. I > find this exceptionally frustrating. I use Python (and Django ORM) to access Postgres. Infinite is not supported. But this is no problem. I look at "Materialized Views" in the wiki: http://wiki.postgresql.org/wiki/Materialized_Views The view gets updated by a trigger. But if the date is infinite, you need to constrain the trigger to the next N years. I guess this is the best solution. Monthly you need to update the view from an external event (maybe cron), to create the missing events for N years + one month... Thank you for your answer Craig, Thomas -- Thomas Guettler, http://www.thomas-guettler.de/ E-Mail: guettli (*) thomas-guettler + de -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
