Re: creating sequential timestamp

javier garcia - CEBAS wrote:
> Is it possible to directly create postgres tables with a timestamp column
> with some specific interval and within a specific range.
> For example every 5 minutes and between the beginning of 1999 and the end of
> 2003?

You could create a function to return that data, and fill a table with
the output. Something like:

create or replace function generate_ts(
timestamp with time zone,
timestamp with time zone,
interval
)
returns setof timestamp with time zone as '
declare
v_start alias for $1;
v_end alias for $2;
v_interim alias for $3;
v_curr timestamp with time zone;
begin
v_curr := v_start;
while v_curr <= v_end loop
return next v_curr;
v_curr := v_curr + v_interim;
end loop;
return;
end;
' language plpgsql;

regression=# select ts from generate_ts('today','tomorrow','3 hours') as
t(ts);
ts
------------------------
2004-03-08 00:00:00-08
2004-03-08 03:00:00-08
2004-03-08 06:00:00-08
2004-03-08 09:00:00-08
2004-03-08 12:00:00-08
2004-03-08 15:00:00-08
2004-03-08 18:00:00-08
2004-03-08 21:00:00-08
2004-03-09 00:00:00-08
(9 rows)

HTH,

Joe