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@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