generate_series() with TSTZRANGE

[Wolfe Whalen]

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!

[Sergey Konoplev]

Post by Wolfe Whalen
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;

Post by Wolfe Whalen
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
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
--
http://www.postgresql.org/mailpref/pgsql-sql

--
Sergey Konoplev

a database and software architect
http://www.linkedin.com/in/grayhemp

Jabber: ***@gmail.com Skype: gray-hemp Phone: [phone]

[Wolfe Whalen]

That's much better, thank you!
--
Wolfe Whalen

Post by Sergey Konoplev

Post by Wolfe Whalen
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;

Post by Wolfe Whalen
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
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
--
http://www.postgresql.org/mailpref/pgsql-sql

--
Sergey Konoplev
a database and software architect
http://www.linkedin.com/in/grayhemp