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