From: | Alban Hertroys <haramrae(at)gmail(dot)com> |
---|---|
To: | Kevin Waterson <kevin(dot)waterson(at)gmail(dot)com> |
Cc: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>, "pgsql-general(at)postgresql(dot)org >> PG-General Mailing List" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Recurring and non recurring events. |
Date: | 2015-12-27 11:46:41 |
Message-ID: | 1748F17F-76C2-40F7-8446-221B823AD28B@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
> On 26 Dec 2015, at 13:03, Kevin Waterson <kevin(dot)waterson(at)gmail(dot)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 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.
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2015-12-27 18:11:37 | Re: grep -f keyword data query |
Previous Message | Andreas Kretschmer | 2015-12-27 10:23:00 | Re: grep -f keyword data query |