Re: Recurring events

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