How to query recurring appointments stored as iCal RRULE in a database table?

Question

If I have a table of appointments with the following columns, how do i query the rrule to pull out appointments that occur on a specific date or between two dates?

Appointments
------
id
name
dt_start
dt_end
rrule

So for example lets say I have an appointment that starts on 2016-09-28 and ends on 2017-04-28 and it occurs every 2 weeks on Monday, Friday until April 28, 2017. The following would be the RRule:

RRULE:FREQ=WEEKLY;INTERVAL=2;UNTIL=20170428T230000Z;BYDAY=MO,FR EXDATE:20170414T023000Z

So using the above example, some of the dates the above appointment would occur on would include the following:

2016-09-30 FRI
2016-10-10 MO
2016-10-14 FRI
2016-10-24 MO

Now how do I query this table using SQL or any other method to pull out all appointments that occur on 2016-10-10?

For the record this would be done in C# asp.net and SQL server. Is there a C# or SQL Server library that I can use to parse and query the rrule?

I've had a look at ICAL.net but there doesn't seem to be much documentation on it or how I'd go about using it for the purpose as explained above.

Does any one have any experience with ICAL.net?

Answer 1

Does any one have any experience with ICAL.net?

Non but I'm the author of php-rrule and I think my answer will apply to your situation too.

Let's start by saying that, unless you have a very restricted set of rules and you know, for example, that no event will ever be infinite, or repeat more than X times or whatever, expanding the rule before storage is not going to work.

As far as I know, you can't do it only in SQL, you have to do it in two steps.

1. Query all the events where dt_start is or is before 2016-10-10 and dt_end is or is after 2016-10-10.
2. Discard all the events that won't occur on 2016-10-10 because of their rule (e.g. BYDAY). For this you will need a library (such as ICAL.net I guess) that can quickly calculate occurrences from the rrule field.

I recommend that you cache the result once this is done, so for any subsequent query you will be able to read the cache for 2016-10-10 instead of doing the calculation again. You have to take care of invalidating the cache anytime an event that includes 2016-10-10 between dt_start and dt_end is added, updated or deleted.