Beefy Boxes and Bandwidth Generously Provided by pair Networks
Do you know where your variables are?

storing and 'understanding' complex calendar events (including recurring events)

by schweini (Friar)
on May 26, 2006 at 19:58 UTC ( [id://551930] : perlquestion . print w/replies, xml ) Need Help??

schweini has asked for the wisdom of the Perl Monks concerning the following question:

dear monks,

I'm playing around with programming a little community-driven 'night life' website, and the core functionality is planned to be the listing of current and upcoming events.
My question is what the most efficient and elegant way of storing events in a database(mysql) would be - including recurring events of different types ('every 2nd tuesday', 'every 3rd monday of a month', 'every 15th of a month'). My main problem is how to store this information in a database in a way that would allow me to efficiently find out if an event applies to a given date. I thought about pre-calculating actual event-dates, but this strikes me as very 'ugly'.
I tried a couple of searches, but maybe i used the wrong keywords or something, because i didn't find anything regarding the efficancy of storing and retrieving this kind of information.
Thanks in advance for any help or pointers to modules, and stuff like that,

  • Comment on storing and 'understanding' complex calendar events (including recurring events)

Replies are listed 'Best First'.
Re: storing and 'understanding' complex calendar events (including recurring events)
by gellyfish (Monsignor) on May 26, 2006 at 20:33 UTC

    You might consider something like Data::ICal or Data::ICal::DateTime These have the side benefit that the files can be read by other programs such as calendar programs and so forth.

    UPdate: Just to make it clear the iCalendar specification already allows for recurrences and the other common things you might want from a calendar and these modules (and others) provide an interface to those files.


Re: storing and 'understanding' complex calendar events (including recurring events)
by socketdave (Curate) on May 26, 2006 at 20:12 UTC
    Check out Date::Manip.

    If you give it a specification for a recurring event it can give you a list of future matching dates. The format isn't as simple as your example but it can do some cool stuff. This may at least be a good place to start.
      Date:Manip is the best....

      Check especially Date:Manip:ParseRecur()
      Dates are stored in an array
      my $start = '01/01/2006'; my $end = '12/31/2010'; my @fourth_tuesdays = ParseRecur("0:1*4:2:0:0:0", $start,$start,$end,) +; my @last_workday_of_every_month = ParseRecur("0:1*0:-1:0:0:0*PWD", $st +art,$start,$end,);

      I haven't found anything nearly as good...
        so this would mean that pre-calculating dates would be my only real option, i guess? Assuming that i have a couple of hundred events, there would be no other way to simply call something like
        @events = showEvents( $date );
Re: storing and 'understanding' complex calendar events (including recurring events)
by CountZero (Bishop) on May 26, 2006 at 22:21 UTC
    I would have (at least) four tables in my database:
    1. The table with all events (recurring and not recurring)
    2. The table with all dates on which an event will take place
    3. A linking table between the "event" table and the "date" table.
    4. The table with the formula for recurring events.

    Every so often (say once a week for all future events and immediately once a new recurring event is entered) the table with recurring events is read and the dates for these events (or only for the new recurring event -- as the case may be) for the next x months are calculated and put in the dates-table and linking table. At the same time, past events are deleted from all tables as necessary.

    I find this a good balance between fast look-up and keeping your tables simple, lean and easy to maintain.


    "If you have four groups working on a compiler, you'll get a 4-pass compiler." - Conway's Law

Re: storing and 'understanding' complex calendar events (including recurring events)
by dsheroh (Monsignor) on May 26, 2006 at 20:42 UTC
    When I did basically the same thing some years ago, I handled recurring events by adopting a slightly-modified cron syntax, which is pretty simple for the code to figure out, although it can admittedly be a little tricky for humans to get used to. (i.e., It's generally not obvious that "3rd Monday of the month" should be represented by indicating that day of week is Monday and day of month is in the range 15-21 unless you've dealt with cron before.)
Re: storing and 'understanding' complex calendar events (including recurring events)
by arkturuz (Curate) on May 27, 2006 at 10:20 UTC
    This may be OT, but I just stumbled upon this module. It might be of help.