> On 26 Dec 2015, at 13:03, Kevin Waterson <kevin.waterson@xxxxxxxxx> 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 reason decides 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 - implementations differ 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 alternative approach. 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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
