Thread: Recurring and non recurring events.

On Sat, Dec 26, 2015 at 23:03:30 +1100,
  Kevin Waterson <kevin.waterson@gmail.com> wrote:
>Thanks, as I am new to postgres, I was unaware of this function.
>To go with this, I guess I will need a table with which to store intervals,
>start and end dates?

There is are built in range types that might be more efficiebt for
indexing rather than using separate start and stop times. See:
http://www.postgresql.org/docs/9.5/static/rangetypes.html#RANGETYPES-BUILTIN

Pleas don't top post - see comment at the bottom of this email.

On 27/12/15 01:03, Kevin Waterson wrote:
> Thanks, as I am new to postgres, I was unaware of this function.
> 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;
>
>
> or something??
>
> Kind regards
> Kevin
>
>
> On Sat, Dec 26, 2015 at 7:22 PM, Pavel Stehule
> <pavel.stehule@gmail.com <mailto:pavel.stehule@gmail.com>> wrote:
>
>     Hi
>
>     2015-12-26 8:28 GMT+01:00 Kevin Waterson <kevin.waterson@gmail.com
>     <mailto:kevin.waterson@gmail.com>>:
>
>         I wish to set up a table of recurring, and non-recurring events.
>         I have been looking at
>         http://justatheory.com/computers/databases/postgresql/recurring_events.html
>         which looks nice (complex but nice) and wonder if there was a
>         better option for this in more recent pgsql versions.
>
>
[...]

In this list, the convention is to post replies at the end (with some
rare exceptions), or interspersed when appropriate, and to omit parts no
longer relevant.

The motivation of bottom posting like this: is that people get to see
the context before the reply, AND emails don't end up getting longer &
longer as people reply at the beginning forgetting to trim the now
irrelevant stuff at the end.


Cheers,
Gavin

Gavin Flower <GavinFlower@archidevsys.co.nz> writes:
> The motivation of bottom posting like this: is that people get to see
> the context before the reply, AND emails don't end up getting longer &
> longer as people reply at the beginning forgetting to trim the now
> irrelevant stuff at the end.

Of course, this also requires that people have the discipline to trim
as much as possible of what they're quoting.  Otherwise, not only do
the messages get longer and longer anyway, but you have to scroll to the
bottom to find what's new.

The general rule for proper email quoting is to quote just enough to
remind readers what the context is.  You are not trying to create a
complete archive of the whole thread in every message; we have email
archives for that.

And the reason why this is worth doing is that it shows respect for
your readers' time.  I'm not sure how many people look at each message
in a popular list like pgsql-general, but surely it's measured in the
thousands.  If you spend a few minutes judiciously cutting quotes and
interspersing your responses in a logical fashion, that may save each
reader only a few seconds in reading/understanding your message, but
that's still a large net savings of time.

            regards, tom lane

> 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.

On Sat, Dec 26, 2015 at 03:15:50PM -0500, Tom Lane wrote:
> Gavin Flower <GavinFlower@archidevsys.co.nz> writes:
> > The motivation of bottom posting like this: is that people get to see
> > the context before the reply, AND emails don't end up getting longer &
> > longer as people reply at the beginning forgetting to trim the now
> > irrelevant stuff at the end.
>
> Of course, this also requires that people have the discipline to trim
> as much as possible of what they're quoting.  Otherwise, not only do
> the messages get longer and longer anyway, but you have to scroll to the
> bottom to find what's new.
>
> The general rule for proper email quoting is to quote just enough to
> remind readers what the context is.  You are not trying to create a
> complete archive of the whole thread in every message; we have email
> archives for that.
>
> And the reason why this is worth doing is that it shows respect for
> your readers' time.  I'm not sure how many people look at each message
> in a popular list like pgsql-general, but surely it's measured in the
> thousands.  If you spend a few minutes judiciously cutting quotes and
> interspersing your responses in a logical fashion, that may save each
> reader only a few seconds in reading/understanding your message, but
> that's still a large net savings of time.

Jumping in late here, but I am getting concerned that most web and
mobile email readers make it difficult to inline quote stuff.  Trimming
text is particularly hard on mobile devices.  As more people use
web-based or mobile email clients, will the "nice" type of email
formatting become rarer and rarer?

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+ Roman grave inscription                             +