Support for EXDATE

[jamesdixon]

Hi, great extension! I was curious if it supports EXDATE? Thanks!

[petropavel13]

Hi! Unfortunately, I know nothing about EXDATE. Can you give me a link with more info about EXDATE? Thanks!

[groner]

Hi @jamesdixon. I'm currently prototyping an application using pg_rrule. Here's how I'm currently planning to handle exdates:

Given a table with fields dtstart timestamptz, rrules rrule[] and exdates timestamptz[], you can do something like this:

SELECT start
FROM
    unnest(rrules) rrules(rrule),
    lateral unnest(get_occurrences(rrule, dtstart, until)) occurence(start)
WHERE
    exdates IS NULL OR start != any(exdates)

Creating some higher level functions to expand events/availability records seems like a good idea.

[nsb]

I just installed this extension and it works great.

Libical appears to support EXDATE https://github.com/libical/libical/blob/master/src/libical/icalproperty_cxx.h#L180 Does this function add an EXDATE to an RRULE?

Does get_occurrences already work if the RRULE contains an EXDATE?

[nsb]

More about EXDATE http://www.kanzaki.com/docs/ical/exdate.html

[petropavel13]

@nsb I think you can run following script to check it:

SELECT * FROM
     unnest(
         get_occurrences('FREQ=WEEKLY;INTERVAL=1;WKST=MO;UNTIL=20200101T045102Z;BYDAY=SA;BYHOUR=10;BYMINUTE=51;BYSECOND=2;EXDATE=20191214T105102Z'::rrule,
             '2019-12-07 10:51:02+00'::timestamp with time zone)
     );

expected result is: 2019-12-07 10:51:02+00 2019-12-21 10:51:02+00 2019-12-28 10:51:02+00

[nsb]

It doesn't seem to support EXDATE. I get this:

    2019-12-07 10:51:02+00
    2019-12-14 10:51:02+00
    2019-12-14 10:51:00+00
    2019-12-21 10:51:02+00
    2019-12-21 10:51:00+00
    2019-12-28 10:51:02+00
    2019-12-28 10:51:00+00

[petropavel13]

sorry. you should change "BYSECOND=2,EXDATE" to "BYSECOND=2;EXDATE"

[nsb]

I get this error now:

ERROR:  Can't parse RRULE. iCal error: MALFORMEDDATA: An input string was not correctly formed or a component has missing or extra properties. RRULE "FREQ=WEEKLY;INTERVAL=1;WKST=MO;UNTIL=20200101T045102Z;BYDAY=SA;BYHOUR=10;BYMINUTE=51;BYSECOND=2;EXDATE=20191214T105102Z".
LINE 3:          get_occurrences('FREQ=WEEKLY;INTERVAL=1;WKST=MO;UNT...
                                 ^
HINT:  You need to omit "RRULE:" part of expression (if present)

[mattdeluco]

The reason it doesn't work is that EXDATE and EXRULE aren't part of an RRULE - I believe pg_rrule only uses the RRULE parsing function from libical (icalrecurrencetype_from_string()). EXDATE and EXRULE are separate properties of ical; this is pg_rrule, not pg_ical.

For example, note below how RRULE and EXDATE are separate properties:

BEGIN:VCALENDAR
VERSION:2.0
METHOD:PUBLISH
PRODID:ical2list
BEGIN:VEVENT
DTSTART:20121201T230000Z
RRULE:FREQ=DAILY
UID:so13662467
DTSTAMP:20121201T230000Z
EXDATE:20121202T230000Z,20121204T230000Z,20121206T230000Z
END:VEVENT
END:VCALENDAR

Given that RRULE and EXDATE/EXRULE are in fact separate fields, one might consider that support doesn't belong in pg_rrule. Instead, what we ought to be doing is storing the exceptions in the database, much like the ical record stores exceptions separately. Consider this modified example from the pg_rrule documentation:

SELECT * FROM unnest(
    get_occurrences(
        'FREQ=WEEKLY;INTERVAL=1;WKST=MO;UNTIL=20200101T045102Z;BYDAY=SA;BYHOUR=10;BYMINUTE=51;BYSECOND=2'::rrule,
        '2019-12-07 10:51:02+00'::timestamp with time zone
    )
)
EXCEPT SELECT '2019-12-28 05:51:02-05'::TIMESTAMP WITH TIME ZONE;

The result:

         unnest         
------------------------
 2019-12-21 05:51:02-05
 2019-12-07 05:51:02-05
 2019-12-14 05:51:02-05
(3 rows)

In the original result there are four rows, the last being the exception I added to the modified query above.

If you consider ical itself to define entries like rows of a database, this makes sense. The RRULE might be one column of your table, arrays of EXDATE values another column, and an EXRULE another column still. As per the ical spec, the final output consists of the set difference between the dates generated by the RRULE and the union of EXDATE and EXRULE values.

If you want to use EXRULE, I imagine you could put the EXRULE through get_occurrences() and subtract that set from the set generated by your RRULE.

[thomastthai]

@groner, I'm trying to learn from your post from 10/21/2015. Would you help me understand a few things I'm confused about?

SELECT start

I don't see start from the columns in your table.

FROM unnest(rrules) rrules(rrule),

What that line do? I think unnest(rrules) turns the rrules array into rows.

What does rrules(rrule) do? What does "rrule" inside the reference?

lateral unnest(get_occurrences(rrule, dtstart, until)) occurence(start)

That was the first time I saw the lateral join. I will read up on it. What purpose does "occurence(start)" serve?

Thank you for helping me learn.