Formulating iCalendar in SQL
2 minutes read

I am currently working on something which hopefully end up being a good calendar server. The goal is to make it easy to add new frontends such as a CalDAV frontend, a Web frontend, etc.

So far, I am just working on getting the data model right. The iCalendar RFC has a data model which I am trying to formulate into SQL, something which is ending up being quite hard. Shannon Clark blogs about why calendars are hard, and all of those issues are issues I am running up against when trying to make the data model.

The main problem seems to be related to time zones, more closely: “How do you store timezone information in your database”. The easy (but wrong) solution is to just normalise everything to UTC. This will not handle the case of events crossing timezone changes, such as to or from daylight savings.

What I am currently considering is making sure all data in the database is stored in UTC, but also store the original time zone information. While a bit more complex, this allows applications to get back the same timezone they put in (I am not convinced Evolution will be happy if the timezones are renamed, for instance) and it allows my application to generate recurring events properly. My main issue with this is having the application responsible for making sure the data in the database makes sense, but without putting half my app in PL/perl, I can not see a way around that.

Oh, and if anybody has great ideas on how to represent timezones in a relational database (postgres), please do mail me or grab me on IRC or something similar.

Back to posts