Using postgres generate_series to generate a recurring schedule

Question

I have a table with recurring dates called Events, how can I take this table and generate the specific upcoming dates from a series based on the events wday and time? (eg only wday "mondays" and start time "7pm" are important)

Event

+-----+---------------------------+---------------------+
| id  | start_at                  | recurring_schedule  |
+-----+---------------------------+---------------------+
| 358 | 2015-01-23 20:00:00 +0000 | Weekly              |
| 359 | 2016-01-22 19:30:00 +1100 | Monthly             |
| 360 | 2016-02-01 19:00:00 +1100 | Weekly              |
| 361 | 2016-02-01 20:00:00 +0000 | Weekly              |
| 362 | 2014-02-13 20:00:00 +0000 | Bi-Weekly           |
+-----+---------------------------+---------------------+

• start_at (date time, used for the DOW and start time, actual date is ignored)

For simplicity you can ignore the recurring schedule and just assume all events are weekly, eg always on the same day of the week.

How can I take a table like this and convert it into something like this:

FutureLog

+----------+---------------------------+
| event_id | start_at                  |
+----------+---------------------------+
| 35       | 2018-04-11 19:30:00 +0000 |
| 94       | 2018-04-12 20:00:00 +0100 |
| 269      | 2018-04-13 18:30:00 +0100 |
| 45       | 2018-04-13 20:00:00 +0100 |
| 242      | 2018-04-13 19:30:00 +1100 |    
| 35       | 2018-04-18 19:30:00 +0000 |
| 94       | 2018-04-19 20:00:00 +0100 |
| 269      | 2018-04-20 18:30:00 +0100 |
| 45       | 2018-04-20 20:00:00 +0100 |
| 242      | 2018-04-20 19:30:00 +1100 |
+----------+---------------------------+

Eg I want to SELECT FROM events the theoretical events and create 6-8 weeks of future dates from each event using something like generate_series to build a timeline of events.

Answer 1

updated answer:

Use generate_series within a case statement to build sequences similarly to ohw I did it in my original answer, with varying frequencies based on the column recurring_schedule.

Specify the date you want the series to generate as an absolute date '2020-01-01'::timestamptz as I've used below, of you can pass a relative date, e.g. NOW() + INTERVAL '10 weeks' instead.

SELECT id event_id, start_at, 
CASE recurring_schedule 
    WHEN 'Weekly' 
        THEN GENERATE_SERIES(start_at, '2020-01-01'::timestamptz, '1 weeks'::INTERVAL)
    WHEN 'Bi-Weekly'
        THEN GENERATE_SERIES(start_at, '2020-01-01'::timestamptz, '2 weeks'::INTERVAL)
    WHEN 'Monthly'
        THEN GENERATE_SERIES(start_at, '2020-01-01'::timestamptz, '1 month'::INTERVAL)
    ELSE NULL 
END recurring_start_time
FROM events;

original answer for schema with json fields:

the syntax for generate_series for datetime types is

generate_series(start_time, end_time, step_interval)

Since your schedule is in JSON contains the interval, you could construct the query as such, and add more schedule types as required.

WITH test (id, start_at, place_id, recurring_schedule) AS (

VALUES 
(358, '2015-01-23 20:00:00 +0000'::TIMESTAMPTZ, 412, 
'{"validations":{"day":[2]},"rule_type":"IceCube::WeeklyRule","interval":1,"week_start":0}'::JSONB),

(359, '2016-01-22 19:30:00 +1100', 414,
'{"validations":{"day":[1]},"rule_type":"IceCube::WeeklyRule","interval":1,"week_start":0}'),

(360, '2016-02-01 19:00:00 +1100', 415, 
'{"validations":{"day":[4]},"rule_type":"IceCube::WeeklyRule","interval":1,"week_start":0}'),

(361, '2016-02-01 20:00:00 +0000', 416, 
'{"validations":{"day":[4]},"rule_type":"IceCube::WeeklyRule","interval":1,"week_start":0}'),

(362, '2014-02-13 20:00:00 +0000', 417,
'{"validations":{"day":[2]},"rule_type":"IceCube::WeeklyRule","interval":1,"week_start":0}')
)
SELECT id, start_at, place_id, 
CASE recurring_schedule->>'rule_type' 
    WHEN 'IceCube::WeeklyRule' 
        THEN GENERATE_SERIES(start_at, NOW(), (recurring_schedule->>'interval' || ' WEEK')::INTERVAL)
    ELSE NULL 
END recurring_start_time
FROM test;