Re: Recurring and non recurring events.

> On 26 Dec 2015, at 13:03, Kevin Waterson <kevin.waterson@gmail.com> wrote:
>
> Thanks, as I am new to postgres, I was unaware of this function.

Actually, the article you referenced makes use of generate_series as well (at INSERT INTO events), but then for some
reasondecides to create a generate_recurrences function later on. Possibly the choice came from them using a domain
(RECURRENCE)that did not translate directly (although almost) to an interval. 

> To go with this, I guess I will need a table with which to store intervals, start and end dates?
>
> eg
> CREATE table events(
>     id serial primary key,
>     start_timestamp timestamp,
>     end_timestamp timestamp,
>     interval
>
> with dateRange as
>   (
>   SELECT min(start_timestamp) as first_date, max(start_timestamp) as last_date
>   FROM events
>   )
> select
>     generate_series(first_date, last_date, '1 hour'::interval)::timestamp as date_hour
> from dateRange;

But, instead of generate_series you could also use a recursive CTE (which is more or less standard SQL -
implementationsdiffer slightly between databases): 

with recursive dateRange (curr_stamp, max_stamp, step) as (
    select min(start_timestamp), max(start_timestamp), interval '1 week'
      from events
    union all
    select curr_stamp + step, max_stamp, step
      from dateRange
     where curr_stamp + step <= max_stamp
)
select curr_stamp from dateRange;

I suspect generate_series is faster, but since your query already almost looked like this I thought I'd offer this
alternativeapproach. It has a little bit more flexibility too, as you can add fields and calculations to the CTE quite
easily.

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.