Spiking iCal recurrence rules in SQL

recurrence.sql

-- TODO: perhaps better approach is to generate a sequence of days,
-- weeks, months and years.  Then join them.
--
-- TODO: I think I want the 'week of month' to work more like the
-- 'recurrence week index', in that it could still respect "real" weeks. I
-- think the same questions can be answered that way while being a little
-- less surprising (that a string of 7 1s doesn't mean Mon-Sun).

CREATE OR REPLACE FUNCTION recurrence_days (start_at timestamp, end_at timestamp, timezone text)
    RETURNS TABLE (
        timezone text,
        day timestamp,
        recurrence_day_index int,
        recurrence_week_index int,
        day_of_week int,
        week_of_month int
    )
    AS $$
    SELECT
        timezone AS timezone,
        day AT TIME ZONE timezone AS day,
        recurrence_day_index,
        DIV(INT8(EXTRACT(dow FROM start_at AT TIME ZONE timezone)) + recurrence_day_index, 7) AS recurrence_week_index,
        EXTRACT(dow FROM day) AS day_of_week,
        CASE
            WHEN EXTRACT(day FROM day) < 8 THEN 0
            WHEN EXTRACT(day FROM day) < 15 THEN 1
            WHEN EXTRACT(day FROM day) < 22 THEN 2
            WHEN EXTRACT(day FROM day) < 29 THEN 3
            ELSE 4
        END AS week_of_month
    FROM (
        SELECT
            (start_at AT TIME ZONE timezone + INTERVAL '1 day' * recurrence_day_index) AS day,
            recurrence_day_index
        FROM
            GENERATE_SERIES(
                0,
                INT8(EXTRACT(days FROM (end_at AT TIME ZONE timezone - start_at AT TIME ZONE timezone))) - 1,
                1
            ) AS recurrence_day_index
    ) AS days;

$$
LANGUAGE SQL;

SELECT
    *
FROM
    recurrence_days ('2022-09-01T10:00', '2022-11-01T10:00', 'Australia/Brisbane');