Join GitHub today
GitHub is home to over 20 million developers working together to host and review code, manage projects, and build software together.
Fetching latest commit…
Cannot retrieve the latest commit at this time.
|Failed to load latest commit information.|
= 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