Re: temporal variants of generate_series()

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
THENceil(           (               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