{"id":385,"date":"2023-06-22T14:53:50","date_gmt":"2023-06-22T21:53:50","guid":{"rendered":"https:\/\/aklaver.org\/wordpress\/?p=385"},"modified":"2023-06-22T14:59:14","modified_gmt":"2023-06-22T21:59:14","slug":"using-icalendar-rrule-in-postgres","status":"publish","type":"post","link":"https:\/\/aklaver.org\/wordpress\/2023\/06\/22\/using-icalendar-rrule-in-postgres\/","title":{"rendered":"Using iCalendar RRULE in Postgres"},"content":{"rendered":"\n

RRULE<\/a> is an iCalendar specification for computing recurring calendar events. Something like, on first Tuesday of each month pay this bill. This is done as a rule that generates the occurrences of the event as needed. This solves the issue of a continually recurring event having to be physically stored as set of occurrences. The link above has some examples and there is this site RRULE generator<\/a> where you can explore the options. This post will be a light introduction on how to store to, retrieve from a Postgres database the rules using Python and Javascript. Then use that information to populate a Javascript calendar in a Flask application. For Python the rrule<\/a><\/strong> module of the dateutil<\/a> program will be used. In Javascript the rrule.js<\/a> program which is a port of dateutil.rrule.<\/p>\n\n\n\n

Setting up Python dateutil:<\/p>\n\n\n

\nfrom dateutil.parser import parse\nfrom dateutil.rrule import *\n\nall for rrule is\n\n["rrule", "rruleset", "rrulestr",\n\n"YEARLY", "MONTHLY", "WEEKLY", "DAILY",\n\n"HOURLY", "MINUTELY", "SECONDLY",\n\n"MO", "TU", "WE", "TH", "FR", "SA", "SU"]\n<\/pre><\/div>\n\n\n
Examples.<\/strong> <\/p>\n\n\n\n
Note the use of count. This is good habit to get into until you are
sure of what the rule is going to produce. Unless you want to produce an
infinite list of occurrences and bring your computer to its knees:). Don’t ask
me how I know.<\/p>\n\n\n\n
Start at dstart and reoccur every month on same day of month for five occurences.<\/p>\n\n\n
\nlist(rrule(freq=MONTHLY, count=5, dtstart=parse("06\/22\/23")))\n\n[datetime.datetime(2023, 6, 22, 0, 0),\n datetime.datetime(2023, 7, 22, 0, 0),\n datetime.datetime(2023, 8, 22, 0, 0),\n datetime.datetime(2023, 9, 22, 0, 0),\n datetime.datetime(2023, 10, 22, 0, 0)]\n\n<\/pre><\/div>\n\n\n
Same as above but specify occurrences to be on 31st of month. This skips month with < 31 days as the RRULE specification requires incorrect dates and\/or times to be skipped not ’rounded’ down.<\/p>\n\n\n
\nlist(rrule(freq=MONTHLY, bymonthday=31, count=5, dtstart=parse("06\/22\/23")))\n\n[datetime.datetime(2023, 7, 31, 0, 0),\n datetime.datetime(2023, 8, 31, 0, 0),\n datetime.datetime(2023, 10, 31, 0, 0),\n datetime.datetime(2023, 12, 31, 0, 0),\n datetime.datetime(2024, 1, 31, 0, 0)]\n\n<\/pre><\/div>\n\n\n
bymonthday supports negative indexing, so to get last day of month regardless of its day number use -1.<\/p>\n\n\n
\nlist(rrule(freq=MONTHLY, bymonthday=-1, count=5, dtstart=parse("06\/22\/23")))\n\n[datetime.datetime(2023, 6, 30, 0, 0),\n datetime.datetime(2023, 7, 31, 0, 0),\n datetime.datetime(2023, 8, 31, 0, 0),\n datetime.datetime(2023, 9, 30, 0, 0),\n datetime.datetime(2023, 10, 31, 0, 0)]\n\n<\/pre><\/div>\n\n\n
To get a better idea of what is possible I recommend looking at the examples
here rrule examples<\/a><\/p>\n\n\n\n
Incorporating RRULE into Postgres.<\/strong><\/p>\n\n\n\n
Create database table to hold rules and associated information.<\/p>\n\n\n
\nCREATE TABLE public.rrule_example(\n    task_id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,\n    task_title varchar NOT NULL,\n    task_desc varchar NOT NULL,\n    task_rrule varchar NOT NULL,\n    start_date date NOT NULL,\n    until_date date\n);\n<\/pre><\/div>\n\n\n
Underlying RRULE is a string format that is fully explained in the RFC<\/a>. The quick and dirty way to derive that in dateutil.rrule is to use the str() method on a rrule.<\/p>\n\n\n
\nr = rrule(freq=WEEKLY, interval=2,   dtstart=parse("06\/22\/2023"))\n\nr.__str__()\n'DTSTART:20230622T000000\\nRRULE:FREQ=WEEKLY;INTERVAL=2'\n<\/pre><\/div>\n\n\n

Insert string form of rrule into database.<\/p>\n\n\n
\nINSERT INTO public.rrule_example OVERRIDING SYSTEM VALUE VALUES (1, 'Every two weeks', 'Task occurrs every two weeks on Thursday', E'DTSTART:20230622T000000\\nRRULE:FREQ=WEEKLY;INTERVAL=2', '2023-06-22', NULL);\n\nselect * from rrule_example;\n-[ RECORD 1 ]----------------------------------------\ntask_id    | 1\ntask_title | Every two weeks\ntask_desc  | Task occurrs every two weeks on Thursday\ntask_rrule | DTSTART:20230622T000000                 +\n           | RRULE:FREQ=WEEKLY;INTERVAL=2\nstart_date | 06\/22\/2023\nuntil_date | NULL\n\n<\/pre><\/div>\n\n\n
Create function to find next rule occurrence using plpython3u procedural language.<\/p>\n\n\n
\nCREATE OR REPLACE FUNCTION public.rrule_next_occurrence(t_rrule character\nvarying, start_dt timestamp with time zone)\nRETURNS timestamp with time zone\nLANGUAGE plpython3u\nSECURITY DEFINER\nAS $function$\nfrom datetime import datetime\nfrom dateutil.parser import parse\nfrom dateutil.rrule import rrulestr\n\nrule = rrulestr(t_rrule, ignoretz=True)\nnext_occ = rule.after(parse(start_dt, ignoretz=True), inc=True)\n\nreturn next_occ\n\n$function$\n;\n<\/pre><\/div>\n\n\n
The function uses dateutil.rrulestr  to parse the string version of the rrule. Then the after() method to find first occurrence of rule after specified date.<\/p>\n\n\n
\nselect rrule_next_occurrence(task_rrule, '2023-06-21') from rrule_example where task_id =1;\n\nrrule_next_occurrence  \n-------------------------\n 06\/22\/2023 00:00:00 PDT\n<\/pre><\/div>\n\n\n
Create function to find previous rule occurrence.<\/p>\n\n\n
\nCREATE OR REPLACE FUNCTION public.rrule_prior_occurrence(t_rrule character\nvarying, start_dt timestamp with time zone)\nRETURNS timestamp with time zone\nLANGUAGE plpython3u\nSECURITY DEFINER\nAS $function$\nfrom datetime import datetime\nfrom dateutil.parser import parse\nfrom dateutil.rrule import rrulestr\n\nrule = rrulestr(t_rrule, ignoretz=True)\nprior_occ = rule.before(parse(start_dt, ignoretz=True), inc=True)\n\nreturn prior_occ\n\n$function$\n;\n<\/pre><\/div>\n\n\n
<\/p>\n\n\n\n
Use rrulestr to parse string rrule. Then before() to find last occurrence of
rule before specified date.<\/p>\n\n\n
\nselect rrule_prior_occurrence(task_rrule, '2023-06-23') from rrule_example where task_id =1;\n\n rrule_prior_occurrence  \n-------------------------\n 06\/22\/2023 00:00:00 PDT\n\n<\/pre><\/div>\n\n\n
<\/p>\n\n\n\n
Using this information in a Web page.<\/p>\n\n\n\n
Using Flask set up FullCalendar(https:\/\/fullcalendar.io\/) calendar to display recurring
events using rrule.js(https:\/\/github.com\/jakubroztocil\/rrule).<\/p>\n\n\n\n
Need to include rrule-tz.js first then the FullCalendar rrule plugin.<\/p>\n\n\n
\n<!--rrule.js with timezone support-->\n<script type=""text\/javascript" src="{{ url_for('static', \nfilename='js\/external\/rrule\/rrule-tz.js') }}"><\/script>\n<script type=""text\/javascript" src="{{ url_for('static', \nfilename='js\/external\/full_calendar\/main.js') }}"><\/script>\n<!--FullCalendar rrule plugin-->\n<script type=""text\/javascript" src="{{ url_for('static', \nfilename='js\/external\/rrule\/main.global.js') }}"><\/script>\n<\/pre><\/div>\n\n\n
In calendar constructor eventSources is where the calendar gets the information
to fill in the calendar.<\/p>\n\n\n
\n<script>\n\n      document.addEventListener('DOMContentLoaded', function() {\n        var calendarEl = document.getElementById('calendar');\n        var calendar = new FullCalendar.Calendar(calendarEl, {\n            timeZone: "US\/Pacific",\n            slotMinTime: "07:00",\n            slotMaxTime: "19:00",\n            slotDuration: "00:15:00",\n            forceEventDuration: true,\n            defaultTimedEventDuration: "00:15",\n            initialView: "dayGridMonth",\n            headerToolbar: {\n                left: "prev,next today, prevYear,nextYear",\n                center: "title",\n                right: "dayGridMonth,timeGridWeek,timeGridDay"\n                },\n            stickyHeaderDates: true,\n            eventSources: [\n                {\n                   url: "\/task_calendar_data",\n                },\n                {events: \n                    [{\n                        title: 'Weekly Mon\/Fri',\n                        rrule: {\n                            freq: 'weekly',\n                            interval: 1,\n                            byweekday: [ 'mo', 'fr' ],\n                            dtstart: '2023-06-01T10:30:00', \n                            until: '2023-10-31'\n                        }\n                    }],\n                    id: "fixed_event"\n\n                \n                }\n            ]\n        });\n        calendar.render();\n      });\n\n    <\/script>\n<\/pre><\/div>\n\n\n
In this case there are two sources url which fetches from a view in Flask and
events which is a fixed event that uses the rrule.js syntax to build an event.<\/p>\n\n\n\n
The view is:<\/p>\n\n\n
\n@calendar_bp.route("\/task_calendar_data")\ndef taskCalendarData():\n    today_dt = date.today()\n    start_dt = request.args.get("start", today_dt.strftime("%m\/%d\/%Y"))\n    end_dt = request.args.get("end",\n                              (today_dt\n                               + timedelta(days=1)).strftime("%m\/%d\/%Y"))\n    # The connection(con) returned from get_db() uses cursor_factory=RealDictCursor\n    # so results are returned as dictionaries.\n    con = db.get_db()\n    cur = con.cursor()\n    cur.execute("select * from rrule_example")\n    rs = cur.fetchall()\n    tasks = []\n    if rs:\n        for task in rs:\n            tasks.append({"id": task["task_id"], "title": task["task_title"], \n                          "rrule": task["task_rrule"], "allDay": True})\n    response = current_app.response_class(\n                response=json.dumps(tasks),\n                mimetype='application\/json'\n            )\n    return response\n\n<\/pre><\/div>\n\n\n
allDay is set True to pin the task to 00:00.<\/p>\n\n\n\n
Insert a rrule that shows an occurrence on last day of month.<\/p>\n\n\n
\nINSERT INTO\n    public.rrule_example OVERRIDING SYSTEM VALUE\nVALUES \n(2, 'Last day of month', 'Task occurrs last day of each month',\nE'DTSTART:20230622T000000\\nRRULE:FREQ=MONTHLY;BYMONTHDAY=-1', \n'2023-06-22', NULL);\n\n<\/pre><\/div>\n\n\n
The calendar display for the rrules inserted into the database and from the eventSources in the calendar constructor.  The current month and October 2023 when the rrule in the calendar constructor ends.<\/p>\n\n\n\n
\"\"<\/a><\/figure>\n\n\n\n
\"\"<\/a><\/figure>\n","protected":false},"excerpt":{"rendered":"
RRULE is an iCalendar specification for computing recurring calendar events. Something like, on first Tuesday of each month pay this bill. This is done as a rule that generates the occurrences of the event as needed. This solves the issue … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"ngg_post_thumbnail":0,"footnotes":""},"categories":[5],"tags":[],"class_list":["post-385","post","type-post","status-publish","format-standard","hentry","category-postgres"],"_links":{"self":[{"href":"https:\/\/aklaver.org\/wordpress\/wp-json\/wp\/v2\/posts\/385","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/aklaver.org\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/aklaver.org\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/aklaver.org\/wordpress\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/aklaver.org\/wordpress\/wp-json\/wp\/v2\/comments?post=385"}],"version-history":[{"count":74,"href":"https:\/\/aklaver.org\/wordpress\/wp-json\/wp\/v2\/posts\/385\/revisions"}],"predecessor-version":[{"id":463,"href":"https:\/\/aklaver.org\/wordpress\/wp-json\/wp\/v2\/posts\/385\/revisions\/463"}],"wp:attachment":[{"href":"https:\/\/aklaver.org\/wordpress\/wp-json\/wp\/v2\/media?parent=385"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/aklaver.org\/wordpress\/wp-json\/wp\/v2\/categories?post=385"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/aklaver.org\/wordpress\/wp-json\/wp\/v2\/tags?post=385"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}