Message-Id: 
 <1347560039.7008.140661127553573.6B093229@webmail.messagingengine.com>
From: Wolfe Whalen <wolfe@quios.net>
To: Sergey Konoplev <gray.ru@gmail.com>
Cc: Postgres SQL List <pgsql-sql@postgresql.org>
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Content-Type: text/plain
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>
Subject: Re: generate_series() with TSTZRANGE
Date: Thu, 13 Sep 2012 11:13:59 -0700

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