temporal variants of generate_series()
I've written the following function definitions to extend
generate_series to support some temporal types (timestamptz, date and
time). Please include them if there's sufficient perceived need or
value.
-- timestamptz version
CREATE OR REPLACE FUNCTION generate_series
( start_ts timestamptz
, end_ts timestamptz
, step interval
) RETURNS SETOF timestamptz
AS $$
DECLARE
current_ts timestamptz := start_ts;
BEGIN
IF start_ts < end_ts AND step > INTERVAL '0 seconds' THEN
LOOP
IF current_ts > end_ts THEN
RETURN;
END IF;
RETURN NEXT current_ts;
current_ts := current_ts + step;
END LOOP;
ELSIF end_ts < start_ts AND step < INTERVAL '0 seconds' THEN
LOOP
IF current_ts < end_ts THEN
RETURN;
END IF;
RETURN NEXT current_ts;
current_ts := current_ts + step;
END LOOP;
END IF;
END;
$$ LANGUAGE plpgsql IMMUTABLE;
-- date version
CREATE OR REPLACE FUNCTION generate_series
( start_ts date
, end_ts date
, step interval
) RETURNS SETOF date
AS $$
DECLARE
current_ts date := start_ts;
BEGIN
IF start_ts < end_ts AND step > INTERVAL '0 seconds' THEN
LOOP
IF current_ts > end_ts THEN
RETURN;
END IF;
RETURN NEXT current_ts;
current_ts := current_ts + step;
END LOOP;
ELSIF end_ts < start_ts AND step < INTERVAL '0 seconds' THEN
LOOP
IF current_ts < end_ts THEN
RETURN;
END IF;
RETURN NEXT current_ts;
current_ts := current_ts + step;
END LOOP;
END IF;
END;
$$ LANGUAGE plpgsql IMMUTABLE;
-- time version
CREATE OR REPLACE FUNCTION generate_series
( start_ts time
, end_ts time
, step interval
) RETURNS SETOF time
AS $$
DECLARE
current_ts time := start_ts;
BEGIN
IF step > INTERVAL '0 seconds' THEN
LOOP -- handle wraparound first
IF current_ts < end_ts THEN
EXIT;
END IF;
RETURN NEXT current_ts;
current_ts := current_ts + step;
END LOOP;
LOOP
IF current_ts > end_ts THEN
RETURN;
END IF;
RETURN NEXT current_ts;
current_ts := current_ts + step;
END LOOP;
ELSIF step < INTERVAL '0 seconds' THEN
LOOP -- handle wraparound first
IF current_ts > end_ts THEN
EXIT;
END IF;
RETURN NEXT current_ts;
current_ts := current_ts + step;
END LOOP;
LOOP
IF current_ts < end_ts THEN
RETURN;
END IF;
RETURN NEXT current_ts;
current_ts := current_ts + step;
END LOOP;
END IF;
END;
$$ LANGUAGE plpgsql IMMUTABLE;
On Thu, 2007-04-12 at 14:56 -0700, Andrew Hammond wrote:
I've written the following function definitions to extend
generate_series to support some temporal types (timestamptz, date and
time). Please include them if there's sufficient perceived need or
value.
I could see these being useful, but a PL/PgSQL implementation is not
eligible for inclusion in the core backend (since PL/PgSQL is not
enabled by default).
-Neil
On Thu, Apr 12, 2007 at 02:56:24PM -0700, Andrew Hammond wrote:
I've written the following function definitions to extend
generate_series to support some temporal types (timestamptz, date and
time). Please include them if there's sufficient perceived need or
value.-- timestamptz version
CREATE OR REPLACE FUNCTION generate_series
( start_ts timestamptz
, end_ts timestamptz
, step interval
) RETURNS SETOF timestamptz
AS $$
DECLARE
current_ts timestamptz := start_ts;
BEGIN
IF start_ts < end_ts AND step > INTERVAL '0 seconds' THEN
LOOP
IF current_ts > end_ts THEN
RETURN;
END IF;
RETURN NEXT current_ts;
current_ts := current_ts + step;
END LOOP;
ELSIF end_ts < start_ts AND step < INTERVAL '0 seconds' THEN
LOOP
IF current_ts < end_ts THEN
RETURN;
END IF;
RETURN NEXT current_ts;
current_ts := current_ts + step;
END LOOP;
END IF;
END;
$$ LANGUAGE plpgsql IMMUTABLE;
Here's an SQL version without much in the way of bounds checking :)
CREATE OR REPLACE FUNCTION generate_series (
start_ts timestamptz,
end_ts timestamptz,
step interval
) RETURNS SETOF timestamptz
LANGUAGE sql
AS $$
SELECT
CASE
WHEN $1 < $2 THEN
$1
WHEN $1 > $2 THEN
$2
END + s.i * $3 AS "generate_series"
FROM generate_series(
0,
floor(
CASE
WHEN $1 < $2 AND $3 > INTERVAL '0 seconds' THEN
extract('epoch' FROM $2) -
extract('epoch' FROM $1)
WHEN $1 > $2 AND $3 < INTERVAL '0 seconds' THEN
extract('epoch' FROM $1) -
extract('epoch' FROM $2)
END/extract('epoch' FROM $3)
)::int8
) AS s(i);
$$;
It should be straight-forward to make similar ones to those below.
CREATE OR REPLACE FUNCTION generate_series
( start_ts date
, end_ts date
, step interval
) RETURNS SETOF date-- time version
CREATE OR REPLACE FUNCTION generate_series
( start_ts time
, end_ts time
, step interval
) RETURNS SETOF time
Cheers,
D
--
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter
Remember to vote!
Consider donating to PostgreSQL: http://www.postgresql.org/about/donate
On Apr 28, 2007, at 8:00 PM, David Fetter wrote:
Here's an SQL version without much in the way of bounds checking :)
CREATE OR REPLACE FUNCTION generate_series (
start_ts timestamptz,
end_ts timestamptz,
step interval
) RETURNS SETOF timestamptz
LANGUAGE sql
AS $$
SELECT
CASE
WHEN $1 < $2 THEN
$1
WHEN $1 > $2 THEN
$2
END + s.i * $3 AS "generate_series"
FROM generate_series(
0,
floor(
CASE
WHEN $1 < $2 AND $3 > INTERVAL '0 seconds' THEN
extract('epoch' FROM $2) -
extract('epoch' FROM $1)
WHEN $1 > $2 AND $3 < INTERVAL '0 seconds' THEN
extract('epoch' FROM $1) -
extract('epoch' FROM $2)
END/extract('epoch' FROM $3)
)::int8
) AS s(i);
$$;It should be straight-forward to make similar ones to those below.
Are you sure the case statements are needed? It seems it would be
better to just punt to the behavior of generate_series (esp. if
generate_series eventually learns how to count backwards).
--
Jim Nasby jim@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
Jim Nasby <decibel@decibel.org> writes:
Are you sure the case statements are needed? It seems it would be
better to just punt to the behavior of generate_series (esp. if
generate_series eventually learns how to count backwards).
What's this "eventually"?
regression=# select * from generate_series(10,1,-1);
generate_series
-----------------
10
9
8
7
6
5
4
3
2
1
(10 rows)
regards, tom lane
On Tue, May 01, 2007 at 05:08:45PM -0400, Tom Lane wrote:
Jim Nasby <decibel@decibel.org> writes:
Are you sure the case statements are needed? It seems it would be
better to just punt to the behavior of generate_series (esp. if
generate_series eventually learns how to count backwards).What's this "eventually"?
regression=# select * from generate_series(10,1,-1);
generate_series
-----------------
10
9
8
7
6
5
4
3
2
1
(10 rows)regards, tom lane
Good point. I believe the function below does the right thing. When
given decreasing TIMESTAMPTZs and a negative interval, it will
generate them going backward in time. When given increasing
TIMESTAMPTZs and a positive interval, it will generate them going
forward in time. Given a 0 interval, it errors out, although not with
the same message as generate_series(1,1,0), and decreasing
TIMESTAMPTZs and a positive interval or vice versa, it generates no
rows.
CREATE OR REPLACE FUNCTION generate_series (
start_ts timestamptz,
end_ts timestamptz,
step interval
) RETURNS SETOF timestamptz
STRICT
LANGUAGE sql
AS $$
SELECT
$1 + s.i * $3 AS "generate_series"
FROM generate_series(
CASE WHEN $1 <= $2
THEN 0
ELSE
floor(
(
extract('epoch' FROM $2) - extract('epoch' FROM $1)
)/extract('epoch' FROM $3)
)::int8
END,
CASE WHEN $1 <= $2
THEN ceil(
(
extract('epoch' FROM $2) - extract('epoch' FROM $1)
)/extract('epoch' FROM $3)
)::int8
ELSE
0
END,
sign(
extract('epoch' FROM $2) - extract('epoch' FROM $1)
)::int8
) AS s(i)
ORDER BY s.i ASC
;
$$;
--
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter
Remember to vote!
Consider donating to PostgreSQL: http://www.postgresql.org/about/donate
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
Import Notes
Resolved by subject fallback
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
Import Notes
Resolved by subject fallback
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))::bigintCREATE 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
--
Jim Nasby jim@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
Jim Nasby <decibel@decibel.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@decibel.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@nasby.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@decibel.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.phpThat'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@nasby.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@postgresql.org so that your
message can get through to the mailing list cleanly
--
Bruce Momjian <bruce@momjian.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@decibel.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.phpThat'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@nasby.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@postgresql.org so that your
message can get through to the mailing list cleanly
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +