postgres-rrule

postgres-rrule is a Postgres extension to make it easier to work with recurring dates in the database.

It parses RRULE statements, and generates occurrences.

Install

Execute postgres-rrule.sql in your database:

  $ psql -X -f postgres-rrule.sql

Or

  $ make all

And modify your search path to include _rrule schema:

  SET search_path TO public, _rrule;

How it works

We create a table (in a newly created _rrule schema) called RRULE to generate start dates from rule criteria. The table constraints enforce the validity of dates in the table.

All of the types and functions are created in that schema. You can parse or query RRULE specifications.

On a sidenote, the current implementation does not support endlessly reapeating events. Please make sure that you include the UNTIL or COUNT rrule to check for multiple occurences.

Example

Parsing

newuser@newuser >> select '
    DTSTART:19970902T090000
    RRULE:FREQ=WEEKLY;UNTIL=19980902T090000
    '::TEXT::RRULESET;
┌───────────────────────────────────────────────────────────────────────────────┐
│                                   rruleset                                    │
├───────────────────────────────────────────────────────────────────────────────┤
│ ("1997-09-02 09:00:00",,"(WEEKLY,1,,""1998-09-02 09:00:00"",,,,,,,,,,MO)",,,) │
└───────────────────────────────────────────────────────────────────────────────┘
(1 row)

Querying RRULESET.

newuser@newuser >> select '
    DTSTART:19970902T090000
    RRULE:FREQ=WEEKLY;UNTIL=19980902T090000
    '::TEXT::RRULESET @> '19970902T090000'::timestamp;
┌──────────┐
│ ?column? │
├──────────┤
│ t        │
└──────────┘
(1 row)

Also works with jsonb

newuser@newuser >> select '{"dtend": "1997-09-03T09:00:00", "rrule": {"freq": "WEEKLY", "wkst": "MO", "count": 4, "interval": 1}, "dtstart": "1997-09-02T09:00:00"}'::text::jsonb::rruleset @> '19970902T090000'::timestamp;
┌──────────┐
│ ?column? │
├──────────┤
│ t        │
└──────────┘
(1 row)

RRULE Operators

Operator	Description	Notes	Result
=	equal	All parameters match	boolean
<>	not equal	Any parameters don't match	boolean
@>	contains rrule	All occurrences generated by second rule would also be generated by first rule	boolean
<@	contained by rrule		boolean

RRULESET Operators

Operator	Description	Notes	Result
@>	contains timestamp	The date of the timestamp is generated by the ruleset.	boolean

RRULESET[] Operators

Operator	Description	Notes	Result
@>	contains timestamp	The date of the timestamp is generated by one of the rulesets in the array.	boolean

RRULESET::jsonb Operators

Operator	Description	Notes	Result
@>	contains timestamp	The date of the timestamp is generated by ruleset.	boolean

RRULESET[]::jsonb Operators

Operator	Description	Notes	Result
@>	contains timestamp	The date of the timestamp is generated by one of the rulesets in the jsonb array.	boolean

Functions

In the case of the rrule functions, there is a second required argument of type timestamp, which is the "dtstart" argument of a rruleset. There is a form for each rrule-accepting function that accepts a text value, and parses it.

Testing

Requires pgTAP and pg_resolve. First let's install those from CPAN.

  $ sudo cpan TAP::Parser::SourceHandler::pgTAP

Now you can run the tests with

  $ make all test

Prior Art

This is a fork of Matthew Schinckel's original repo.

License

The MIT License (MIT)

Copyright (c) 2015 Matthew Schinckel, 2019 Volkan Unsal