Re: generate_series() with TSTZRANGE

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