recurring_events_for: A Ruby repository from bakineggs

= recurring_events_for

  A PostgreSQL function for determining dates
  and times when a recurring event will occur.

== Usage

  The recurring_events_for function takes 4 parameters:

    1. Range Start (TIMESTAMP)
      This parameter specifies the date and time before which no recurrences
      should be returned. If a recurrence starts before this time but ends after
      it, the recurrence will be returned. This should be in UTC.
  
    2. Range End (TIMESTAMP)
      This parameter specifies the date and time after which no recurrences
      should be returned. If a recurrence ends after this time but starts before
      it, the recurrence will be returned. This should be in UTC.
  
    3. Time Zone (CHARACTER VARYING)
      This specifies the time zone which should be used when determining if an
      all day event occurs within the given range.

    4. Event Limit (INTEGER)
      As an optimization over running a query such as
        SELECT * from recurring_events_for(...) LIMIT x
      you can include a parameter to limit the recurrences that are returned,
      instead of returning them all and then limiting what was selected. Pass
      in NULL for this parameter if you do not want a limit.

  The function returns one row for each occurrence of an event. The events
  table columns starts_on/ends_on or starts_at/ends_at will be the date/time
  of that occurrence. starts_at/ends_at will be adjusted for DST such that
  the event will always start at the same time in the event's time zone. This
  means that the utc time of the event will change over DST boundaries.

== How Events And Recurrence Patterns Are Stored

  events table:

    starts_on (DATE), ends_on (DATE):
      The first (or only) date that an event occurs should be stored in the
      starts_on column. For multi-day events, set the ends_on column to
      the final day of the event.

    starts_at (TIMESTAMP), ends_at (TIMESTAMP):
      The first (or only) timespan that an event occurs should be stored in
      the starts_at and ends_at columns. These columns should not be used if
      the starts_on or ends_on columns are also in use.

    frequency (CHARACTER VARYING):
      This column specifies the frequency at which this event recurs. If no
      specific rules for this event are given in the event_recurrences table,
      the event will recur on this frequency after the original date/time.
      Possible values are 'once', 'daily', 'weekly', 'monthly', and 'yearly'.

    separation (INTEGER):
      The number of intervals at en event's frequency in between occurrences of
      the event. For instance, if an event occurs every other week, it has a
      frequency of weekly and a separation of 2 because there are 2 weeks in
      between occurrences. This column defaults to 1.

    count (INTEGER):
      Specifies a limit number of times the event will occur. Set this column to
      NULL for no limit.

    until (TIMESTAMP):
      Specifies a limiting date and time after which no recurrences will be
      generated for this event. Set this column to NULL for no limit.

  event_recurrences table:

    for daily recurring events:
      This table is not used for daily recurring events. Adding entries to this
      table for a daily recurring event will cause unspecified results.

    for weekly recurring events:

      day (INTEGER):
        The day of the week the event occurs.
        0 = Sunday, 1 = Monday, ..., 6 = Saturday

      week (INTEGER), month (INTEGER):
        These columns should be set to NULL for weekly recurring events.
        Setting these columns to non-NULL values will cause unspecified results.

    for monthly recurring events:

      week (INTEGER):
        If non-NULL, this specifies the week of the month in which the event
        occurs. Positive numbers indicate the week from the start of the month.
        1 = 1st week of the month, 2 = 2nd week of the month, etc.
        Negative numbers indicate the week before the end of the month.
        -1 = last week of the month, -2 = 2nd to last week of the month, etc.

      day (INTEGER):
        If the week column is NULL, the day column specifies the day of the
        month that the event occurs. If the week column is non-NULL, the day
        column specifies the day of the week that the event occurs in that week
        of the month.

      month (INTEGER):
        This column should be set to NULL for monthly recurring events.
        Setting this column to a non-NULL value will cause unspecified results.

    for yearly recurring events:

      month (INTEGER):
        If the month column is non-NULL, it specifies the month for which this
        pattern should be used. If it is NULL, this pattern will be for the
        month of the original date/time of the event.

      week (INTEGER), day (INTEGER):
        The usage for the week and day columns of a yearly recurring event are
        exactly the same as their usage for monthly recurring events.

  event_cancellations table:

    date (DATE):
      The date of the recurrence of an event which should be cancelled. If the
      event spans multiple days, this column should be set to the first date on
      which the recurrence to be cancelled falls.

== Running Tests

  The tests for recurring_events_for are written in Ruby using Rspec. If you
  have Ruby and Rspec installed, you can run the tests by executing "spec spec"
  in the top level directory of this project.

  You may have to manually create the DB 'recurring_events_test'.

  You may also have to execute the following if the system complains about
  the language not existing:

  CREATE PROCEDURAL LANGUAGE plpgsql;

== Credits

  This function was originally posted on David Wheeler (justatheory)'s blog:
  http://www.justatheory.com/computers/databases/postgresql/recurring_events.html

  It has been forked by Dan Barry (bakineggs.com) in a GitHub repository:
  http://github.com/danbarry/recurring_events_for
bakineggs/recurring_events_for
