volkanunsal / postgres-rrule Goto Github PK

View Code? Open in Web Editor NEW

103.0 6.0 26.0 62 KB

Recurring dates in Postgres.

License: MIT License

Makefile 1.19% PLpgSQL 98.81%

postgres ical rrule

postgres-rrule's Introduction

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

  $ 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)

postgres-rrule's People

Contributors

Stargazers

Watchers

postgres-rrule's Issues

ERROR: type "rruleset" does not exist

Not working with postgres 14

select '
    DTSTART:19970902T090000
    RRULE:FREQ=WEEKLY;UNTIL=19980902T090000
    '::TEXT::RRULESET;
ERROR:  type "rruleset" does not exist
LINE 4:     '::TEXT::RRULESET;

I don't want to compare time; just dates

Here is the thing I am trying to test:
select 'RRULE:FREQ=WEEKLY;COUNT=30;INTERVAL=1;WKST=MO;BYDAY=MO'::TEXT::_rrule.RRULESET @> '20200824T090000'::timestamp;

It should be true as the date I am checking for is Monday, but I get false in return. I think this is because you're comparing time as well. How can I get the expected true result?

test_rrule_helpers.sql

tests/test_rrule_occurrences.sql .. 1/7
# Failed test 5: "testYearlyByMonthDay"
#     Results differ beginning at row 2:
#         have: ("1998-09-01 09:00:00")
#         want: ("1997-10-01 09:00:00")
# Failed test 7: "testYearlyByWeekDay"
#     Results differ beginning at row 1:
#         have: ("1997-03-02 09:00:00")
#         want: ("1997-09-02 09:00:00")

Daylight savings support

Currently the occurrences() function (and possibly others) does not respect daylight savings. This means that a recurrence rule that crosses daylight savings will report wrong times for occurrences in the new time zone.

iCAL uses the VTIMEZONE field to specify the event creation timezone, it should be handled and report the correct times.

As an example run this query:

SELECT _rrule.occurrences(
    _rrule.rrule('RRULE:FREQ=WEEKLY;INTERVAL=1;BYDAY=WE;UNTIL=20221105T000000Z'),
    '2022-10-26T05:00:00'::timestamp,
    '[2022-10-24, 2022-11-04]'::tsrange
);

This is the current transition from CEST to CET in europe, canonically this is the Europe/Belgrade time zone.
There is currently no way to pass that information to the function. It should return the following occurrences: 2022-10-26T05:00:00+02:00, 2022-10-26T05:00:00+01:00 if we assume the returned time zone is UTC (currently it returns the time without time zone).

Does not repeat within the defined tsrange

select *
FROM
  occurrences('
    DTSTART:20200301T000000
    RRULE:FREQ=WEEKLY'::TEXT::rruleset, tsrange('2020-03-01 00:00', '2021-04-01 00:00'));

Produces:

"occurrences"
"2020-03-01 00:00:00"
"2020-03-08 00:00:00"

I expect to get 57 rows (as there are 57 weeks in the time range), but I am only getting 2 rows.

Adding an arbitrary large COUNT "fixes" the problem.

select *
FROM
  occurrences('
    DTSTART:20200301T000000
    RRULE:FREQ=WEEKLY;COUNT=1000'::TEXT::rruleset, tsrange('2020-03-01 00:00', '2021-04-01 00:00'));

Produces:

"occurrences"
"2020-03-01 00:00:00"
"2020-03-08 00:00:00"
"2020-03-15 00:00:00"
"2020-03-22 00:00:00"
"2020-03-29 00:00:00"
"2020-04-05 00:00:00"
"2020-04-12 00:00:00"
"2020-04-19 00:00:00"
"2020-04-26 00:00:00"
"2020-05-03 00:00:00"
"2020-05-10 00:00:00"
"2020-05-17 00:00:00"
"2020-05-24 00:00:00"
"2020-05-31 00:00:00"
"2020-06-07 00:00:00"
"2020-06-14 00:00:00"
"2020-06-21 00:00:00"
"2020-06-28 00:00:00"
"2020-07-05 00:00:00"
"2020-07-12 00:00:00"
"2020-07-19 00:00:00"
"2020-07-26 00:00:00"
"2020-08-02 00:00:00"
"2020-08-09 00:00:00"
"2020-08-16 00:00:00"
"2020-08-23 00:00:00"
"2020-08-30 00:00:00"
"2020-09-06 00:00:00"
"2020-09-13 00:00:00"
"2020-09-20 00:00:00"
"2020-09-27 00:00:00"
"2020-10-04 00:00:00"
"2020-10-11 00:00:00"
"2020-10-18 00:00:00"
"2020-10-25 00:00:00"
"2020-11-01 00:00:00"
"2020-11-08 00:00:00"
"2020-11-15 00:00:00"
"2020-11-22 00:00:00"
"2020-11-29 00:00:00"
"2020-12-06 00:00:00"
"2020-12-13 00:00:00"
"2020-12-20 00:00:00"
"2020-12-27 00:00:00"
"2021-01-03 00:00:00"
"2021-01-10 00:00:00"
"2021-01-17 00:00:00"
"2021-01-24 00:00:00"
"2021-01-31 00:00:00"
"2021-02-07 00:00:00"
"2021-02-14 00:00:00"
"2021-02-21 00:00:00"
"2021-02-28 00:00:00"
"2021-03-07 00:00:00"
"2021-03-14 00:00:00"
"2021-03-21 00:00:00"
"2021-03-28 00:00:00"

Occurrences are not calculated correctly across year boundaries

Because all_starts restricts its search space to the end of the year containing dtstart it may sometimes return an empty set. This leads to occurrences also being empty.

e.g:

select * from all_starts('RRULE:FREQ=WEEKLY;INTERVAL=1;BYDAY=TH;COUNT=100'::text::rrule, '2029-12-31T10:00:00'::TIMESTAMP);

returns 0 rows, because it will not find a matching Thursday between dtstart and the end of 2029.

Changing https://github.com/volkanunsal/postgres-rrule/blob/master/src/functions/0017-all_starts.sql#L44 to search beyond year_end (e.g. until 12 months later, same as the subsequent generators) resolves this issue.

Given that the function may already process 365 days (e.g if dtstart was on Jan 1), is it an issue to increase the number of days that are processed here?

@volkanunsal happy to raise a PR if you are still maintaining this project.

Timezone support

Is setting a timezone not supported?

What's the best way to interact with timestamptz and tstzrange

First of all, thanks for sharing this, I'm looking at using it, storing event's rule in a column of my event's table. I've decided to use timestamptz to store utc time as well as tstzrange for an event's time range, all stored as UTC in the database. What would be your suggestion to use your project, which as far as I can tell expects timestamp so far. Would cast be enough? Should these types be added to your project? If yes and you can guide me through it, happy to give it a shot as I integrate and use it. Thanks!

On rruleset, byhour/byminute do not function (value is read from DTSTART/UNTIL)

When setting hour/minute values on rruleset, they are not read properly and instead the DTSTART or UNTIL are read:

These function normally on regular rrule queries though:

Failed Test: "Every day contains every second day"

#     '(DAILY,1,,,,,,,,,,,,)'
#         @>
#     '(DAILY,2,,,,,,,,,,,,)'

The contains operators aren't working very well.

Infinite Events

I come to the problem that when there is no COUNT or UNTIL definied in the RRULE the expected behavior should be that the event is repeated infinitly.

SELECT *
FROM occurrences(
        'RRULE:FREQ=WEEKLY;UNTIL=20250902T090000'::TEXT,
        '2020-04-19T17:50:00'::TIMESTAMP,
        tsrange(now()::timestamp, now()::timestamp + (interval '7d'))
    );

With this Query the Output is the expected Date (2021-05-02 17:50:00.000000) but when I remove the UNTIL statement from the RRULE I don't get any result

SELECT *
FROM occurrences(
        'RRULE:FREQ=WEEKLY'::TEXT,
        '2020-04-19T17:50:00'::TIMESTAMP,
        tsrange(now()::timestamp, now()::timestamp + (interval '7d'))
    );

Does it support nth day rule???

Executing occurrences on rrule RRULE:FREQ=MONTHLY;COUNT=3;INTERVAL=1;BYDAY=1TU
returns the error. Seems like it doesn't understand nth day. I'm not sure if I'm missing anything.

SQL Error [22P02]: ERROR: invalid input value for enum _rrule.day: "1TU"
Where: SQL function "day_array" statement 1
SQL statement "WITH "tokens" AS (
WITH A20 as (SELECT _rrule.parse_line($1::text, 'RRULE') "r"),
-- Split each key value pair into an array, e.g. {'FREQ', 'DAILY'}
A30 as (SELECT regexp_split_to_array("r", '=') AS "y" FROM A20)
SELECT "y"[1] AS "key", "y"[2] AS "val" FROM A30
),
candidate AS (
SELECT
(SELECT "val"::_rrule.FREQ FROM "tokens" WHERE "key" = 'FREQ') AS "freq",
(SELECT "val"::INTEGER FROM "tokens" WHERE "key" = 'INTERVAL') AS "interval",
(SELECT "val"::INTEGER FROM "tokens" WHERE "key" = 'COUNT') AS "count",
(SELECT "val"::TIMESTAMP FROM "tokens" WHERE "key" = 'UNTIL') AS "until",
(SELECT _rrule.integer_array("val") FROM "tokens" WHERE "key" = 'BYSECOND') AS "bysecond",
(SELECT _rrule.integer_array("val") FROM "tokens" WHERE "key" = 'BYMINUTE') AS "byminute",
(SELECT _rrule.integer_array("val") FROM "tokens" WHERE "key" = 'BYHOUR') AS "byhour",
(SELECT _rrule.day_array("val") FROM "tokens" WHERE "key" = 'BYDAY') AS "byday",
(SELECT _rrule.integer_array("val") FROM "tokens" WHERE "key" = 'BYMONTHDAY') AS "bymonthday",
(SELECT _rrule.integer_array("val") FROM "tokens" WHERE "key" = 'BYYEARDAY') AS "byyearday",
(SELECT _rrule.integer_array("val") FROM "tokens" WHERE "key" = 'BYWEEKNO') AS "byweekno",
(SELECT _rrule.integer_array("val") FROM "tokens" WHERE "key" = 'BYMONTH') AS "bymonth",
(SELECT _rrule.integer_array("val") FROM "tokens" WHERE "key" = 'BYSETPOS') AS "bysetpos",
(SELECT "val"::_rrule.DAY FROM "tokens" WHERE "key" = 'WKST') AS "wkst"

EXRULE is not being parsed properly

command:

select _rrule.rruleset('
DTSTART:20230724T100000
RRULE:FREQ=WEEKLY;BYDAY=MO,WE
EXRULE:FREQ=WEEKLY;BYDAY=MO,WE
')

error

SQL Error [P0001]: ERROR: FREQ cannot be null
  Where: PL/pgSQL function _rrule.validate_rrule(_rrule.rrule) line 5 at RAISE
SQL statement "SELECT _rrule.validate_rrule(result)"
PL/pgSQL function _rrule.rrule(text) line 48 at PERFORM
SQL function "rruleset" statement 1

occurences() for rruleset does not respect DTEND

The function that consumes an rruleset and returns occurences passes a tsrange instance of (,) by default, which ignores dtend set in in the rruleset.

The following function fixes this:

CREATE OR REPLACE FUNCTION _rrule.occurrences("rruleset" _rrule.RRULESET)
  RETURNS SETOF TIMESTAMP AS $$
DECLARE
  local_rrule _rrule.rrule := rruleset.rrule;
  tsrange_start TIMESTAMP := rruleset.dtstart;
  tsrange_end TIMESTAMP;
  calculated_range TSRANGE;
BEGIN
  IF rruleset.dtend IS NOT NULL THEN
    tsrange_end := rruleset.dtend;
  ELSIF local_rrule.until IS NOT NULL THEN
    tsrange_end = local_rrule.until;
  END IF;

  IF tsrange_end IS NOT NULL THEN
    calculated_range = tsrange(tsrange_start, tsrange_end);
  ELSE
    calculated_range = tsrange(tsrange_start);
  END IF;

  RETURN QUERY SELECT * FROM _rrule.occurrences("rruleset", calculated_range);
END;
$$ LANGUAGE plpgsql IMMUTABLE STRICT;

SQL Error [22007]

At first, thanks for your great work!

I just wanted to test it out, installed the schema and tried the first example.

select '
    DTSTART:19970902T090000
    RRULE:FREQ=WEEKLY;UNTIL=19980902T090000
    '::TEXT::RRULESET;

But this results in an error which translates to

SQL Error [22007]: ERROR: invalid input syntax for type timestamp: "19970902T090000 RRULE:FREQ=WEEKLY"
Where: SQL function "rruleset" statement 1

I am testing this on my Windows machine.

Original (German) Error

SQL Error [22007]: FEHLER: ungültige Eingabesyntax für Typ timestamp: »19970902T090000    RRULE:FREQ=WEEKLY«
  Where: SQL-Funktion »rruleset« Anweisung 1

Invalid format for the UNTIL field

The following query should return an equivalent string to what is in the input:

SELECT _rrule.text(_rrule.rrule('RRULE:FREQ=WEEKLY;UNTIL=20221105T020000Z;BYDAY=WE'))

It returns the following: RRULE:FREQ=WEEKLY;UNTIL=2022-11-05 02:00:00;BYDAY=WE

The UNTIL field is returned in the form of a postgres timestamp, instead it should look like the example above. Have a look at the specification here.

Excluding hours during the day.

Is it possible to exclude individual hours during the days? E.g. 10-12 at 06/20/2023
I'm building a system where availability is part of it, and sometimes it may be necessary to specify extra hours in a day where a person is unavailable.

interval is ignored when byday is set for weekly frequencies

When creating a rule to repeat every other Monday, rrule seems to suggest a rule like:

FREQ=WEEKLY;INTERVAL=2;BYDAY=MO

Interval works as I would expect when byday is not set:

postgres=# select occurrences('{"freq": "WEEKLY", "wkst": "MO", "until": "2023-08-01", "interval": 2}'::jsonb::rrule, '2023-06-19');
     occurrences
---------------------
 2023-06-19 00:00:00
 2023-07-03 00:00:00
 2023-07-17 00:00:00
 2023-07-31 00:00:00
(4 rows)

However, when byday is set, interval appears to be ignored:

postgres=# select occurrences('{"freq": "WEEKLY", "wkst": "MO", "byday": ["MO"], "until": "2023-08-01", "interval": 2}'::jsonb::rrule, '2023-06-19');
     occurrences
---------------------
 2023-06-19 00:00:00
 2023-06-26 00:00:00
 2023-07-03 00:00:00
 2023-07-10 00:00:00
 2023-07-17 00:00:00
 2023-07-24 00:00:00
 2023-07-31 00:00:00
(7 rows)

Do you expect byday and interval to work together?

It seems to ignore `byweekday` attribute

This is my query:
SELECT '{ "rrule": { "freq": "WEEKLY","wkst": "MO","count": 30,"interval": 1,"byweekday": "RRule.FR"},"dtstart": "2020-09-21" }'::jsonb::_rrule.rruleset @> '2020-09-21'::timestamp

This is resulting in true, but it should be false according to rrule.
I made a rule for every Friday starting from 21st Sept 2020(Monday).