Thread: 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