Skip to content

volkanunsal/postgres-rrule

Folders and files

NameName
Last commit message
Last commit date

Latest commit

 

History

46 Commits
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Repository files navigation

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