Обсуждение: generate_series() with TSTZRANGE

Hi everyone!

I'm new around here, so please forgive me if this is a bit trivial.  It
seems that generate_series() won't generate time stamp ranges.  I
googled around and didn't see anything handy, so I wrote this out and
thought I'd share and see if perhaps there was a better way to do it:

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;

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

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

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