Recurrence Rule

INTRODUCTION:

An RRULE (Recurrence Rule) is a standard way to define recurring events in a calendar. By using RRULEs, you can specify complex repeating
schedules for events. This guide provides an overview of different recurrence frequencies (minutely, hourly, daily, weekly, monthly, and yearly)
and explains each property used to configure these rules.

EXPLANATION OF OCCURRENCES:

Occurrences refer to the instances when an event happens based on the recurrence rule. For example,
if you have a weekly event with a frequency of WEEKLY and an interval of 1, and the rule starts on August 16, 2024, the occurrences will be
every week on the same day and time until the end date or until the count is reached.

COMPONENTS OF RRULE:

  • Start Date: This is the date and time when the first occurrence of the event starts.
  • End Date: This is the date and time when the recurrence of the event stops.
  • Recurring Task Rule Name: This is the name given to the specific recurrence rule to identify it easily.
  • Comments: Additional notes or details about the recurrence rule.
  • Zone Info: This specifies the time zone in which the recurrence is scheduled.

KEY COMPONENTS OF RRULE:

1. Frequency (FREQ)

The FREQ property determines how often the event recurs. Common frequencies are:

  • MINUTELY: Every minute.
  • HOURLY: Every hour.
  • DAILY: Every day.
  • WEEKLY: Every week.
  • MONTHLY: Every month.
  • YEARLY: Every year.

2. Interval (INTERVAL)

The INTERVAL property specifies the interval between occurrences. For example:

  • INTERVAL=2 means the event occurs every 2 units of the frequency (e.g., every 2 minutes, every 2 hours).

3. Until (UNTIL)

The UNTIL property sets an end date for the recurrence. It is specified in the format YYYYMMDDTHHMMSSZ. This property stops the recurrence
after this date. This is automatically derived from the End Date.

4. Count (COUNT)

The COUNT property defines the number of occurrences of the event. For example:

  • COUNT=5 means the event will occur 5 times.

5. ByHour, ByMinute, BySecond

These properties specify the exact time of day when the event occurs:

  • BYHOUR=10: Event occurs at the 10th hour of the day.
  • BYMINUTE=30: Event occurs at the 30th minute.
  • BYSECOND=15: Event occurs at the 15th second.

6. ByDay (BYDAY)

The BYDAY property specifies the days of the week for the recurrence (Sunday, Monday, etc.). For example:

  • BYDAY=MO, TU, WE: Event occurs on Monday, Tuesday, and Wednesday.

7. ByMonthDay (BYMONTHDAY)

The BYMONTHDAY property specifies the day of the month (a range of 1-31 days). For example:

  • BYMONTHDAY=15: Event occurs on the 15th day of each month.

8. ByMonth (BYMONTH)

The BYMONTH property specifies the month of the year. For example:

  • BYMONTH=8: Event occurs in August.

9. ByYearDay (BYYEARDAY)

The BYYEARDAY property specifies the day of the year (range of 1-365). For example:

  • BYYEARDAY=100: Event occurs on the 100th day of the year.

10. ByWeekNo (BYWEEKNO)

The BYWEEKNO property specifies the week number of the year (range of 1-52, corresponding to the 52 weeks of the year). For example:

  • BYWEEKNO=6: Event occurs in the 6th week of the year.

11. BySetPos (BYSETPOS)

The BYSETPOS property specifies the position of a day in a month or week (First, Second, Third, Fourth, Last). For example:

  • BYSETPOS=-1: Event occurs on the last instance of the specified weekday.

12. WeekStart (WKST)

The WKST property defines the start of the week. Common values include:

  • WKST=SU: Sunday.

13. NEVER

This means the event will continue indefinitely until the End Date you have specified.

HOW TO USE THE RRULE GENERATOR:

  1. Select Frequency: Choose the frequency of recurrence from options like minutely, hourly, daily, weekly, monthly, or yearly.
  2. Configure Properties: Adjust properties based on your needs; Properties are shown based on the selected Frequency.
    Given below are properties common for all Frequencies:
    • Interval: Determines how frequently the event recurs.
    • After: This corresponds to the COUNT property, meaning the event will end after a specified number of occurrences.
    • On: This corresponds to the UNTIL property, meaning the event will end on a specific date and time. This is automatically derived from the End Date.
    • Never: This means the event will continue indefinitely until the End Date you have specified.
  3. Specific Dates/Times: Use properties like BYHOUR, BYMINUTE, BYMONTHDAY, etc., to specify exact dates and times.
  4. Generate RRULE: After configuring, generate the RRULE and use it in your calendar applications or systems.
  5. User view: Optional, not required just for user views.
    • Occurrence Duration: The duration for each occurrence of the task; it shows only for Daily, Weekly, Monthly, Yearly FREQ.
    • Recurrence Rule: Displays the RRULE, a standard format used to describe the recurrence pattern.
    • Occurrence End Time: The end time for each occurrence, likely calculated automatically based on the start time and duration.

UNDERSTANDING RECURRENCE FREQUENCIES AND PROPERTIES:

NOTES: After, On, Never, and Interval are common properties that need to be chosen based on your needs. The end must also be selected based
on your needs (After, On, Never) in the end lookup field to determine how the recurrence should end.

1. Minutely Recurrence:

  • Purpose: Defines an event that repeats every minute.
  • Properties:
    • FREQ=MINUTELY: Sets the event to recur every minute.
    • UNTIL=YYYYMMDDTHHMMSSZ: Specifies the end date and time for the recurrence (in UTC).
    • INTERVAL=1: Defines the interval between occurrences. An interval of 1 means every minute.
    • COUNT=n: Specifies the number of occurrences. For example, COUNT=3 will generate 3 occurrences.

Examples:

FREQ=MINUTELY; UNTIL=20240831T235959Z; INTERVAL=1

Repeats every minute until August 31, 2024, 23:59:59 UTC.

FREQ=MINUTELY; COUNT=3; INTERVAL=1

Repeats every minute for 3 occurrences.

FREQ=MINUTELY; INTERVAL=1

Repeats every minute until August 31, 2024, 23:59:59 UTC.

2. Hourly Recurrence:

  • Purpose: Defines an event that repeats every hour.
  • Properties:
    • FREQ=HOURLY: Sets the event to recur every hour.
    • UNTIL=YYYYMMDDTHHMMSSZ: Specifies the end date and time for the recurrence (in UTC).
    • INTERVAL=1: Defines the interval between occurrences. An interval means every hour.
    • COUNT=n: Specifies the number of occurrences. For example, COUNT=1 will generate 1 occurrence.

Examples:

FREQ=HOURLY;COUNT=1; INTERVAL=1

Repeats every hour for 1 occurrence.

3. Daily Recurrence:

  • Purpose: Defines an event that repeats every day.
  • Properties:
    • FREQ=DAILY: Sets the event to recur every day.
    • UNTIL=YYYYMMDDTHHMMSSZ: Specifies the end date and time for the recurrence (in UTC).
    • INTERVAL=1: Defines the interval between occurrences. An interval of means every 1 day.
    • BYHOUR, BYMINUTE, BYSECOND: Specifies the exact time of the event.
    • COUNT=n: Specifies the number of occurrences. For example, COUNT=3 will generate 3 occurrences.

Examples:

FREQ=DAILY; COUNT=3; INTERVAL=4; BYHOUR=0; BYMINUTE=30; BYSECOND=0

Repeats every 4 days for 3 occurrences, starting at 00:30:00 UTC.

4. Weekly Recurrence:

  • Purpose: Defines an event that repeats every week.
  • Properties:
    • FREQ=WEEKLY: Sets the event to recur every week.
    • UNTIL=YYYYMMDDTHHMMSSZ: Specifies the end date and time for the recurrence (in UTC).
    • INTERVAL=1: Defines the interval between occurrences. Every 1 week.
    • BYHOUR, BYMINUTE, BYSECOND: Specifies the exact time of the event.
    • BYDAY: Specifies the days of the week when the event occurs.
    • WKST: Defines the start day of the week.

Examples:

FREQ=WEEKLY; UNTIL=20240831T235959Z; INTERVAL=1; BYHOUR=0; BYMINUTE=30; BYSECOND=0; BYDAY=MO, TH, SA; WKST=SU

Repeats every 1 week on Monday, Thursday, and Saturday at 00:30:00 UTC until August 31, 2024.

5. Monthly Recurrence:

  • Purpose: Defines an event that repeats every month.
  • Properties:
    • FREQ=MONTHLY: Sets the event to recur every month.
    • UNTIL=YYYYMMDDTHHMMSSZ: Specifies the end date and time for the recurrence (in UTC).
    • INTERVAL=1: Defines the interval between occurrences. Every 1 month.
    • BYHOUR, BYMINUTE, BYSECOND: Specifies the exact time of the event.
    • BYMONTHDAY: Specifies the day of the month when the event occurs.
    • BYDAY: Specifies the day of the week within the month.
    • BYSETPOS: Specifies the occurrence position in the month.

Examples:

ON DAY and ON THE has to choose based on your needs:

ON DAY:

FREQ=MONTHLY; UNTIL=20240831T235959Z; INTERVAL=1; BYHOUR=0; BYMINUTE=30; BYSECOND=0; BYMONTHDAY=5

Repeats every 1 month on the 5th day at 00:30:00 UTC until August 31, 2023.

ON THE:

FREQ=MONTHLY; UNTIL=20240831T235959Z; INTERVAL=1; BYHOUR=0; BYMINUTE=30; BYSECOND=0; BYDAY=TH; BYSETPOS=1,2,3,4,-1

Repeats every 1 month on the first, second, third, fourth, and last Thursday at 00:30:00 UTC until August 31, 2023.

6. Yearly Recurrence:

  • Purpose: Defines an event that repeats every year.
  • Properties:
    • FREQ=YEARLY: Sets the event to recur every year.
    • UNTIL=YYYYMMDDTHHMMSSZ: Specifies the end date and time for the recurrence (in UTC).
    • INTERVAL=1: Defines the interval between occurrences. Every 1 year.
    • BYHOUR, BYMINUTE, BYSECOND: Specifies the exact time of the event.
    • BYMONTHDAY: Specifies the day of the month.
    • BYMONTH: Specifies the month of the event. For example, BYMONTH=8 means August.
    • BYDAY: Specifies the day of the week within the month.
    • BYSETPOS: Specifies the occurrence position in the month.
    • BYYEARDAY: Specifies the day of the year.
    • BYWEEKNO: Specifies the week number of the year.

Examples:

ON A MONTH AND DAY, ON A MONTH, WEEK, AND DAY, ON A YEAR DAY, ON A SPECIFIC WEEK OF THE YEAR AND DAY has to choose based on your needs:

ON A MONTH AND DAY:

FREQ=YEARLY; UNTIL=20600831T235959Z; INTERVAL=1; BYHOUR=0; BYMINUTE=30; BYSECOND=0; BYMONTHDAY=4; BYMONTH=8

Repeats every 1 year on August 4th at 00:30:00 UTC until August 31, 2060.

ON A MONTH, WEEK, AND DAY:

FREQ=YEARLY; UNTIL=20600831T235959Z; INTERVAL=1; BYHOUR=0; BYMINUTE=30; BYSECOND=0; BYDAY=FR; BYMONTH=8; BYSETPOS=1,2,3,4,-1

Repeats every 1 year on the first, second, third, fourth, and last Friday of August at 00:30:00 UTC until August 31, 2060.

ON A YEAR DAY:

FREQ=YEARLY; UNTIL=20600831T235959Z; INTERVAL=1; BYHOUR=0; BYMINUTE=30; BYSECOND=0; BYYEARDAY=4

Repeats every 1 year on the 4th day of the year (January 4th) at 00:30:00 UTC until August 31, 2060.

ON A SPECIFIC WEEK OF THE YEAR AND DAY:

FREQ=YEARLY; UNTIL=20600831T235959Z; INTERVAL=1; BYHOUR=0; BYMINUTE=30; BYSECOND=0; BYDAY=SA; BYWEEKNO=6

Repeats every 1 year on the Saturday of the 6th week of the year at 00:30:00 until August 31, 2060.

USING THE iCal.get_occurrences FUNCTION:

The iCal.get_occurrences function is used to calculate the occurrences of events within a specified time range based on a given recurrence rule.
This can be particularly useful for scheduling and calendar applications.

Function Syntax:

SELECT * FROM [iCal]. [get_occurrences] (@event_start_dt, @event_end_dt, @view_start_dt, @view_end_dt, @recurrence_rule, @duration);

Parameters:

  • event_start_dt and event_end_dt: Define the time frame of the event to ensure that the occurrences are generated only within this period.
  • view_start_dt and view_end_dt: Specify the period during which we want to retrieve the occurrences.
  • recurrence_rule: Specifies how often and when the event should recur, providing a clear pattern for generating occurrences.
  • duration: Defines how long each occurrence lasts (if not null).

Example Usage:

We can calculate the occurrences of every event before we created For Example: Yearly recurrence ON A MONTH AND DAY before we created:

DECLARE @view_start_dt DATETIME = '2024-08-16 04:58:57.000';

DECLARE @view_end_dt DATETIME = '2026-08-31 08:15:00.000';

DECLARE @recurrence_rule VARCHAR (255) = 'FREQ=YEARLY; UNTIL=20600831T235959Z; INTERVAL=1; BYHOUR=0; BYMINUTE=30; BYSECOND=0;
BYMONTHDAY=4; BYMONTH=8';

DECLARE @duration VARCHAR (100) = null;

DECLARE @event_start_dt DATETIME = '2024-08-16 04:58:57.000';

DECLARE @event_end_dt DATETIME = '2060-08-31 23:59:59.000';

SELECT * FROM iCal.get_occurrences (@event_start_dt, @event_end_dt, @view_start_dt, @view_end_dt, @recurrence_rule, @duration);

Output:

These occurrences are generated based on the recurrence rule, which specifies that the event recurs yearly on August 4th at 00:30 AM,
within the view_start_dt and view_end_dt.

To view occurrences of events with different frequencies, you can modify the parameters in the iCal.get_occurrences function as per needs.

occurrence_start_dateoccurrence_end_date
2025-08-04 00:30:00.0002025-08-04 00:30:00.000
2026-08-04 00:30:00.0002026-08-04 00:30:00.000