Skip to content

Instantly share code, notes, and snippets.

@bradparker
Last active September 22, 2022 23:20
Show Gist options
  • Save bradparker/7e88074b8861e8900bb4b1ba5481c14a to your computer and use it in GitHub Desktop.
Save bradparker/7e88074b8861e8900bb4b1ba5481c14a to your computer and use it in GitHub Desktop.
Spiking iCal recurrence rules in 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');
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment