forked from bakineggs/recurring_events_for
/
README
130 lines (96 loc) · 5.35 KB
/
README
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
= 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 date or
starts_at/ends_at column(s) will be the date/time of that occurrence.
== How Events And Recurrence Patterns Are Stored
events table:
date (DATE), starts_at (TIMESTAMP), ends_at (TIMESTAMP):
The first (or only) date/time that an event occurs should be stored in
the date column or the combination of starts_at and ends_at columns, but
not both options. The date column should be used for all day events,
while the starts_at and ends_at columns should be used for events that
start and end at specific times.
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.
== 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