Re: generate_series() with TSTZRANGE

That's much better, thank you!

--
Wolfe Whalen
wolfe(at)quios(dot)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(at)fastmail(dot)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(at)quios(dot)net
> >
> >
> > --
> > Sent via pgsql-sql mailing list (pgsql-sql(at)postgresql(dot)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(dot)ru(at)gmail(dot)com Skype: gray-hemp Phone: +79160686204