How would you store possibly recurring times?

Question

I need to store whether something happens once, daily, weekdays, weekly, some days of the week, some days of the month, which may be numerical or symbolic, like first Monday of each month, and so on.

Any recommendations? Any code, data structure or schema to look at?

Answer 1

Try an open source project that has scheduling functionality?

Some scheduling interface/definitions

http://tools.ietf.org/html/rfc4791

and

http://en.wikipedia.org/wiki/ICalendar

Answer 2

This sounds like a "recurring event" such as in Outlook. I would use one table called RecurrenceType to store each time period (daily, weekly, etc.) Another table called Event woud refer by key to RecurrenceType. Future dates for most recurrence types can then be calculated using standard Date functions.

Answer 3

There are complex solutions and easy solutions. The two easiest solutions are:

1. Fan out recurring events up to some constant number of instances, or up to some fixed date range in the future. Store a FK recurrence_id with each instance that points to a description of the recurrence, and allows for mass editing and canceling.

   The advantage of the pre-calced fan out approach is it makes it very very easy to implement recurrence exceptions, which will almost certainly be the first feature request you get.

2. Calculate at display time. Computers are fast, depending on the questions you want to be able to answer about your data, it will often be trivially easy to calculate all the occurrences in a date range. You can be smart and try to quickly bracket your date range before doing the recurrence calculation, or you can brute force it from the onset date.

Beyond that you just need a solution for storing the recurrence rule that works with whatever you're using to calculate recurrences. (e.g. if you're using an iCalendar enable library, your schema is varchar(255) with RRULE values in it)

If you're having to roll your own recurrence calculator, and you want to keep it simple, limiting your recurrences to daily, weekly, monthly, or yearly covers your first 80% use case and is trivially easy to calc.

At which point your potential recurrence schema looks something like:

id
recurrence_start
recurrence_end
type (daily|weekly|monthly|yearly)
day_of_week (for weekly)
month
day_of_month

And frankly the complex solutions probably aren't worth it :)

Answer 4

The problem is that there are infinite possibilities for specifying such an interval. For example - "The first monday of every other month if the date is even, but not 4, and the month isn't February of a long year". How far are you willing to go? Eventually you'll just have to make the users type in a boolean expression which evaluates to TRUE on days that the event is to recur. Not very nice from a UI perspective.

You should decide on some limitations for your system. Once you know those, the rest should be easy - or at least answerable on SO. :)

Answer 5

Martin Fowler wrote a really great paper about this. You can find a lot of the same ideas he discusses in runt, a Ruby library for dealing with temporal expressions.

Answer 6

The schema described here captures recurring dates

http://github.com/bakineggs/recurring%5Fevents%5Ffor