Re: temporal variants of generate_series()

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(at)fetter(dot)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