From: Wolfe Whalen <wolfe@quios.net>
To: Sergey Konoplev <gray.ru@gmail.com>
Cc: Postgres SQL List <pgsql-sql@postgresql.org>
Subject: Re: generate_series() with TSTZRANGE
Date: Thu, 13 Sep 2012 11:13:59 -0700
Message-ID: <1347560039.7008.140661127553573.6B093229@webmail.messagingengine.com> (raw)
In-Reply-To: <CAL_0b1spRknFntwj-E8r+z1jJQeUWcqmt7tS9Wd9s_eRr_EOOw@mail.gmail.com>
References: <1347493990.25573.140661127188777.1658803C@webmail.messagingengine.com>
<CAL_0b1spRknFntwj-E8r+z1jJQeUWcqmt7tS9Wd9s_eRr_EOOw@mail.gmail.com>
That's much better, thank you!
--
Wolfe Whalen
wolfe@quios.net
On Thu, Sep 13, 2012, at 06:52 AM, Sergey Konoplev wrote:
> On Thu, Sep 13, 2012 at 3:53 AM, Wolfe Whalen <wolfe_whalen@fastmail.fm>
> wrote:
> > SELECT tstzrange((lag(a) OVER()), a, '[)')
> > FROM generate_series('2012-09-16 12:00:00'::timestamp, '2012-09-17
> > 12:00:00', '1 hour')
> > AS a OFFSET 1;
>
> What about this form?
>
> select tstzrange(a, a + '1 hour'::interval, '[)')
> from generate_series(
> '2012-09-16'::timestamp,
> '2012-09-16 23:00'::timestamp,
> '1 hour'::interval) as a;
>
> >
> > Basically, it's generating a series of time stamps one hour apart, then
> > using the previous record and the current record to construct the
> > TSTZRANGE value. It's offset 1 to skip the first record, since there is
> > no previous record to pair with it.
> >
> > If you were looking at Josh Berkus' example at
> > http://lwn.net/Articles/497069/ you might use it like this to generate
> > data for testing and experimentation:
> >
> > INSERT INTO room_reservations
> > SELECT 'F104', 'John', 'Another Talk',
> > tstzrange((lag(a) OVER()), a, '[)')
> > FROM generate_series('2012-09-16 12:00:00'::timestamp, '2012-09-17
> > 12:00:00', '1 hour')
> > AS a OFFSET 1;
> >
> > Thanks!
> >
> > --
> > Wolfe Whalen
> > wolfe@quios.net
> >
> >
> > --
> > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-sql
>
>
>
> --
> Sergey Konoplev
>
> a database and software architect
> http://www.linkedin.com/in/grayhemp
>
> Jabber: gray.ru@gmail.com Skype: gray-hemp Phone: +79160686204
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: wolfe@quios.net, gray.ru@gmail.com
Subject: Re: generate_series() with TSTZRANGE
In-Reply-To: <1347560039.7008.140661127553573.6B093229@webmail.messagingengine.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox