Re: temporal variants of generate_series()

Here's a shorter version:

On the date variant, I wasn't sure how to handle intervals with parts
smaller than days:
floor, ceiling, round or error out
To get round, the last parameters of generate_series would be
extract('epoch' FROM '1 day'::interval)::bigint * round(extract('epoch' FROM
$3) / extract('epoch' FROM '1 day'::interval))::bigint

CREATE OR REPLACE FUNCTION generate_series (
start_ts timestamptz,
end_ts timestamptz,
step interval
) RETURNS SETOF timestamptz
STRICT
LANGUAGE sql
AS $$
SELECT
'epoch'::timestamptz + s.i * '1 second'::interval AS "generate_series"
FROM
generate_series(
extract('epoch' FROM $1)::bigint,
extract('epoch' FROM $2)::bigint,
extract('epoch' FROM $3)::bigint
) s(i);
$$;

CREATE OR REPLACE FUNCTION generate_series (
start_ts date,
end_ts date,
step interval
) RETURNS SETOF date
STRICT
LANGUAGE sql
AS $$
SELECT
('epoch'::date + s.i * '1 second'::interval)::date AS "generate_series"
FROM
generate_series(
extract('epoch' FROM $1)::bigint,
extract('epoch' FROM $2)::bigint,
extract('epoch' FROM date_trunc('day', $3))::bigint -- does a floor
) s(i);
$$;

Jean-Pierre Pelletier
e-djuster

On May 2, 2007, at 8:24 PM, JEAN-PIERRE PELLETIER wrote:
> On the date variant, I wasn't sure how to handle intervals with
> parts smaller than days:
> floor, ceiling, round or error out

Hrm... I'm not sure what would be better there... I'm leaning towards
round (floor or ceil don't make much sense to me), but I could also
see throwing an error if trunc('day', $3) != $3. Comments?

Also, what would be the appropriate way to put this into initdb?
These seem a bit long to try and cram into a one-line DATA statement
in pg_proc.h. Should I add a new .sql file ala
information_schema.sql? Is it possible to still add pg_catalog
entries after the postgresql.bki stage of initdb?

Finally, should I also add a timestamp without time zone version? I
know we'll automatically cast timestamptz to timestamp, but then you
get a timestamptz back, which seems odd.

> To get round, the last parameters of generate_series would be
> extract('epoch' FROM '1 day'::interval)::bigint * round(extract
> ('epoch' FROM $3) / extract('epoch' FROM '1 day'::interval))::bigint
>
> CREATE OR REPLACE FUNCTION generate_series (
> start_ts timestamptz,
> end_ts timestamptz,
> step interval
> ) RETURNS SETOF timestamptz
> STRICT
> LANGUAGE sql
> AS $$
> SELECT
> 'epoch'::timestamptz + s.i * '1 second'::interval AS
> "generate_series"
> FROM
> generate_series(
> extract('epoch' FROM $1)::bigint,
> extract('epoch' FROM $2)::bigint,
> extract('epoch' FROM $3)::bigint
> ) s(i);
> $$;
>
> CREATE OR REPLACE FUNCTION generate_series (
> start_ts date,
> end_ts date,
> step interval
> ) RETURNS SETOF date
> STRICT
> LANGUAGE sql
> AS $$
> SELECT
> ('epoch'::date + s.i * '1 second'::interval)::date AS
> "generate_series"
> FROM
> generate_series(
> extract('epoch' FROM $1)::bigint,
> extract('epoch' FROM $2)::bigint,
> extract('epoch' FROM date_trunc('day', $3))::bigint -- does
> a floor
> ) s(i);
> $$;
>
> Jean-Pierre Pelletier
> e-djuster
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
> http://www.postgresql.org/about/donate
>

--
Jim Nasby jim(at)nasby(dot)net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)

Jim Nasby <decibel(at)decibel(dot)org> writes:
> Also, what would be the appropriate way to put this into initdb?

You seem to have missed a step here, which is to convince people that
these belong in core at all. So far I've not even seen an argument that
would justify putting them in contrib. If they *were* of sufficiently
wide use to justify putting them into core, a more efficient
implementation would probably be expected.

regards, tom lane

On May 6, 2007, at 8:07 PM, Tom Lane wrote:
> Jim Nasby <decibel(at)decibel(dot)org> writes:
>> Also, what would be the appropriate way to put this into initdb?
> You seem to have missed a step here, which is to convince people that
> these belong in core at all. So far I've not even seen an argument
> that
> would justify putting them in contrib.

These are all examples of using generate series plus additional math
to generate a series of dates/timestamps:
http://archives.postgresql.org/pgsql-general/2007-01/msg01292.php
http://archives.postgresql.org/pgsql-sql/2006-02/msg00249.php
http://archives.postgresql.org/pgsql-general/2005-06/msg01254.php
http://archives.postgresql.org/pgsql-sql/2007-03/msg00093.php
http://archives.postgresql.org/pgsql-novice/2007-01/msg00002.php
http://archives.postgresql.org/pgsql-sql/2006-03/msg00391.php
http://archives.postgresql.org/pgsql-hackers/2006-09/msg00330.php

That's from the first page of search results for 'generate_series
timestamp'.

FWIW, I could also make use of this in some of my code.

> If they *were* of sufficiently
> wide use to justify putting them into core, a more efficient
> implementation would probably be expected.

Ok, I'll look into a C version, but why do SQL functions have such a
high overhead? I'm seeing an SQL function taking ~2.6x longer than
the equivalent code run directly in a query. With 100 days, the
difference drops a bit to ~2.4x. (this is on HEAD from a few months ago)

This is on my MacBook Pro with the Jean-Pierre's version of
generate_series:

decibel=# select count(*) from generate_series(now(),now()+'10
days'::interval,'1'::interval);
Time: 1851.407 ms
decibel=# select count(*) from generate_series(1,86400*10);
Time: 657.894 ms
decibel=# select count(*) from (select now() + (generate_series
(1,86400*10) * '1 second'::interval)) a;
Time: 733.592 ms
decibel=# select count(*) from (select 'epoch'::timestamptz + s.i *
'1 second'::interval AS "generate_series" from generate_series(extract
('epoch' from now())::bigint, extract('epoch' from now()+'10
days'::interval)::bigint, extract('epoch' from
'1'::interval)::bigint) s(i)) a;
Time: 699.606 ms
--
Jim Nasby jim(at)nasby(dot)net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)

This has been saved for the 8.4 release:

http://momjian.postgresql.org/cgi-bin/pgpatches_hold

---------------------------------------------------------------------------

Jim Nasby wrote:
> On May 6, 2007, at 8:07 PM, Tom Lane wrote:
> > Jim Nasby <decibel(at)decibel(dot)org> writes:
> >> Also, what would be the appropriate way to put this into initdb?
> > You seem to have missed a step here, which is to convince people that
> > these belong in core at all. So far I've not even seen an argument
> > that
> > would justify putting them in contrib.
>
> These are all examples of using generate series plus additional math
> to generate a series of dates/timestamps:
> http://archives.postgresql.org/pgsql-general/2007-01/msg01292.php
> http://archives.postgresql.org/pgsql-sql/2006-02/msg00249.php
> http://archives.postgresql.org/pgsql-general/2005-06/msg01254.php
> http://archives.postgresql.org/pgsql-sql/2007-03/msg00093.php
> http://archives.postgresql.org/pgsql-novice/2007-01/msg00002.php
> http://archives.postgresql.org/pgsql-sql/2006-03/msg00391.php
> http://archives.postgresql.org/pgsql-hackers/2006-09/msg00330.php
>
> That's from the first page of search results for 'generate_series
> timestamp'.
>
> FWIW, I could also make use of this in some of my code.
>
> > If they *were* of sufficiently
> > wide use to justify putting them into core, a more efficient
> > implementation would probably be expected.
>
> Ok, I'll look into a C version, but why do SQL functions have such a
> high overhead? I'm seeing an SQL function taking ~2.6x longer than
> the equivalent code run directly in a query. With 100 days, the
> difference drops a bit to ~2.4x. (this is on HEAD from a few months ago)
>
> This is on my MacBook Pro with the Jean-Pierre's version of
> generate_series:
>
> decibel=# select count(*) from generate_series(now(),now()+'10
> days'::interval,'1'::interval);
> Time: 1851.407 ms
> decibel=# select count(*) from generate_series(1,86400*10);
> Time: 657.894 ms
> decibel=# select count(*) from (select now() + (generate_series
> (1,86400*10) * '1 second'::interval)) a;
> Time: 733.592 ms
> decibel=# select count(*) from (select 'epoch'::timestamptz + s.i *
> '1 second'::interval AS "generate_series" from generate_series(extract
> ('epoch' from now())::bigint, extract('epoch' from now()+'10
> days'::interval)::bigint, extract('epoch' from
> '1'::interval)::bigint) s(i)) a;
> Time: 699.606 ms
> --
> Jim Nasby jim(at)nasby(dot)net
> EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Added to TODO:

* Add temporal versions of generate_series()

http://archives.postgresql.org/pgsql-hackers/2007-04/msg01180.php

---------------------------------------------------------------------------

Jim Nasby wrote:
> On May 6, 2007, at 8:07 PM, Tom Lane wrote:
> > Jim Nasby <decibel(at)decibel(dot)org> writes:
> >> Also, what would be the appropriate way to put this into initdb?
> > You seem to have missed a step here, which is to convince people that
> > these belong in core at all. So far I've not even seen an argument
> > that
> > would justify putting them in contrib.
>
> These are all examples of using generate series plus additional math
> to generate a series of dates/timestamps:
> http://archives.postgresql.org/pgsql-general/2007-01/msg01292.php
> http://archives.postgresql.org/pgsql-sql/2006-02/msg00249.php
> http://archives.postgresql.org/pgsql-general/2005-06/msg01254.php
> http://archives.postgresql.org/pgsql-sql/2007-03/msg00093.php
> http://archives.postgresql.org/pgsql-novice/2007-01/msg00002.php
> http://archives.postgresql.org/pgsql-sql/2006-03/msg00391.php
> http://archives.postgresql.org/pgsql-hackers/2006-09/msg00330.php
>
> That's from the first page of search results for 'generate_series
> timestamp'.
>
> FWIW, I could also make use of this in some of my code.
>
> > If they *were* of sufficiently
> > wide use to justify putting them into core, a more efficient
> > implementation would probably be expected.
>
> Ok, I'll look into a C version, but why do SQL functions have such a
> high overhead? I'm seeing an SQL function taking ~2.6x longer than
> the equivalent code run directly in a query. With 100 days, the
> difference drops a bit to ~2.4x. (this is on HEAD from a few months ago)
>
> This is on my MacBook Pro with the Jean-Pierre's version of
> generate_series:
>
> decibel=# select count(*) from generate_series(now(),now()+'10
> days'::interval,'1'::interval);
> Time: 1851.407 ms
> decibel=# select count(*) from generate_series(1,86400*10);
> Time: 657.894 ms
> decibel=# select count(*) from (select now() + (generate_series
> (1,86400*10) * '1 second'::interval)) a;
> Time: 733.592 ms
> decibel=# select count(*) from (select 'epoch'::timestamptz + s.i *
> '1 second'::interval AS "generate_series" from generate_series(extract
> ('epoch' from now())::bigint, extract('epoch' from now()+'10
> days'::interval)::bigint, extract('epoch' from
> '1'::interval)::bigint) s(i)) a;
> Time: 699.606 ms
> --
> Jim Nasby jim(at)nasby(dot)net
> EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +