Skip site navigation (1) Skip section navigation (2)

Re: temporal variants of generate_series()

From: David Fetter <david(at)fetter(dot)org>
To: Andrew Hammond <andrew(dot)george(dot)hammond(at)gmail(dot)com>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: temporal variants of generate_series()
Date: 2007-04-28 19:00:44
Message-ID: 20070428190044.GC18518@fetter.org (view raw or flat)
Thread:
Lists: pgsql-hackers
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

In response to

Responses

pgsql-hackers by date

Next:From: Martijn van OosterhoutDate: 2007-04-28 19:18:32
Subject: Re: I have made the first step on postgresql, but got some problems
Previous:From: Tom LaneDate: 2007-04-28 16:56:06
Subject: Re: Avoiding unnecessary reads in recovery

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group