From: Wolfe Whalen <wolfe_whalen@fastmail.fm>
To: pgsql-sql@postgresql.org
Subject: generate_series() with TSTZRANGE
Date: Wed, 12 Sep 2012 16:53:10 -0700
Message-ID: <1347493990.25573.140661127188777.1658803C@webmail.messagingengine.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: wolfe_whalen@fastmail.fm
Subject: Re: generate_series() with TSTZRANGE
In-Reply-To: <1347493990.25573.140661127188777.1658803C@webmail.messagingengine.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox