Re: [GENERAL] Recurring and non recurring events.
On Sat, Dec 26, 2015 at 03:15:50PM -0500, Tom Lane wrote: > Gavin Flower 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 http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Roman grave inscription + -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Recurring and non recurring events.
> On 26 Dec 2015, at 13:03, Kevin Waterson 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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Recurring and non recurring events.
Gavin Flower 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Recurring and non recurring events.
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 mailto:pavel.steh...@gmail.com>> wrote: Hi 2015-12-26 8:28 GMT+01:00 Kevin Waterson mailto:kevin.water...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Recurring and non recurring events.
On Sat, Dec 26, 2015 at 23:03:30 +1100, 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? 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Recurring and non recurring events.
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 wrote: > Hi > > 2015-12-26 8:28 GMT+01:00 Kevin Waterson : > >> 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. >> >> All pointers gratefully received. >> > > use generate_series > > postgres=# select v::date from generate_series(current_date, current_date > + 100, interval '7days') g(v); > ┌┐ > │ v │ > ╞╡ > │ 2015-12-26 │ > │ 2016-01-02 │ > │ 2016-01-09 │ > │ 2016-01-16 │ > │ 2016-01-23 │ > │ 2016-01-30 │ > │ 2016-02-06 │ > │ 2016-02-13 │ > │ 2016-02-20 │ > │ 2016-02-27 │ > │ 2016-03-05 │ > │ 2016-03-12 │ > │ 2016-03-19 │ > │ 2016-03-26 │ > │ 2016-04-02 │ > └┘ > (15 rows) > > >> Kev >> > > -- -- "Democracy is two wolves and a lamb voting on what to have for lunch. Liberty is a well-armed lamb contesting the vote."
Re: [GENERAL] Recurring and non recurring events.
Hi 2015-12-26 8:28 GMT+01:00 Kevin Waterson : > 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. > > All pointers gratefully received. > use generate_series postgres=# select v::date from generate_series(current_date, current_date + 100, interval '7days') g(v); ┌┐ │ v │ ╞╡ │ 2015-12-26 │ │ 2016-01-02 │ │ 2016-01-09 │ │ 2016-01-16 │ │ 2016-01-23 │ │ 2016-01-30 │ │ 2016-02-06 │ │ 2016-02-13 │ │ 2016-02-20 │ │ 2016-02-27 │ │ 2016-03-05 │ │ 2016-03-12 │ │ 2016-03-19 │ │ 2016-03-26 │ │ 2016-04-02 │ └┘ (15 rows) > Kev >
[GENERAL] Recurring and non recurring events.
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. All pointers gratefully received. Kev
