pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedgenerate_series() with TSTZRANGE
3+ messages / 3 participants
[nested] [flat]
* generate_series() with TSTZRANGE
@ 2012-09-12 23:53 Wolfe Whalen <wolfe_whalen@fastmail.fm>
0 siblings, 1 reply; 3+ messages in thread
From: Wolfe Whalen @ 2012-09-12 23:53 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: generate_series() with TSTZRANGE
@ 2012-09-13 13:52 Sergey Konoplev <gray.ru@gmail.com>
parent: Wolfe Whalen <wolfe_whalen@fastmail.fm>
0 siblings, 1 reply; 3+ messages in thread
From: Sergey Konoplev @ 2012-09-13 13:52 UTC (permalink / raw)
To: Wolfe Whalen <wolfe_whalen@fastmail.fm>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: generate_series() with TSTZRANGE
@ 2012-09-13 18:13 Wolfe Whalen <wolfe@quios.net>
parent: Sergey Konoplev <gray.ru@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Wolfe Whalen @ 2012-09-13 18:13 UTC (permalink / raw)
To: Sergey Konoplev <gray.ru@gmail.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2012-09-13 18:13 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-09-12 23:53 generate_series() with TSTZRANGE Wolfe Whalen <wolfe_whalen@fastmail.fm>
2012-09-13 13:52 ` Sergey Konoplev <gray.ru@gmail.com>
2012-09-13 18:13 ` Wolfe Whalen <wolfe@quios.net>
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