Re: temporal variants of generate_series()

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))::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
>
>
>
> ---------------------------(end of  
> broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
>                http://www.postgresql.org/about/donate
>

--
Jim Nasby                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)