9

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?

adam78
  • 9,668
  • 24
  • 96
  • 207
  • your going to have to split up the string and extract the different parts, build a table of dates between your start and end (recursive cte) or date dimension would be good. then use a bunch of case statements to identify all of the combinations of dates, interface, days of week, end of month, 1st Thursday of month etc..... this will not be a light task but is doable. – Matt Sep 28 '16 at 18:00

2 Answers2

3

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.

rlanvin
  • 6,057
  • 2
  • 18
  • 24
0

That is a lot of work for the database and application to do if you have a lot of repeating events. In the calendar application I wrote, I expanded the rule when creating the event and have each repeating event in a separate row. I added a unique id field (UID in the icalendar spec, separate from the id field) so I can group the repeating events together in a query if needed (each event in the repeating series has the same unique id). It is then an easy matter to search for dates using a standard query. It also makes it easier to avoid including exceptions if they are in the rule, which would not have a row in the database.

zcontent
  • 316
  • 3
  • 4
  • can you provide an example of your database schema? Wouldn't each repeating event bloat up the database? Lets say there's an event with no end date but repeats indefinitely - this would quickly use up all database storage space. Refer to the following docs: https://github.com/bmoeskau/Extensible/blob/master/recurrence-overview.md – adam78 Sep 28 '16 at 20:01
  • When I create the event, I use a date range that limits the repeating dates to a block of time, like 1 year ago to 3 years from now, then have a function to expand/remove repeating dates as time goes by. Not the best but the queries are quick. Not enough space here to show table schema but you can download the free version of Zap Calendar Lite that has this function and look at the code (requires Joomla) http://zcontent.net/products/zap-calendar-lite – zcontent Sep 29 '16 at 15:44