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
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
