Skip site navigation (1) Skip section navigation (2)

Re: generate_series() with TSTZRANGE

From: Sergey Konoplev <gray(dot)ru(at)gmail(dot)com>
To: Wolfe Whalen <wolfe_whalen(at)fastmail(dot)fm>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: generate_series() with TSTZRANGE
Date: 2012-09-13 13:52:47
Message-ID: CAL_0b1spRknFntwj-E8r+z1jJQeUWcqmt7tS9Wd9s_eRr_EOOw@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-sql
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


In response to

Responses

pgsql-sql by date

Next:From: Tom LaneDate: 2012-09-13 14:19:41
Subject: Re: underscore pattern in a query doens't work
Previous:From: Adrian KlaverDate: 2012-09-13 13:46:03
Subject: Re: pg_restore problem

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group