creating sequential timestamp

On Mon, 8 Mar 2004, javier garcia - CEBAS wrote:

Hi;
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?

I just need this column.

Sounds like you might want to rethink your data structure. I would
suggest using a table with these dates in it as a kind of cross tab table.
Or look in the /contrib/tablefunc directory for a crosstab function that
may make what you're trying to do easy.

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

Hi all;

First of all, thanks to Joe Conway for this function;

I've loaded it in the server but when I try to use it exactly as in the
example Joe gives or in other way I obtain the answer:
---------------------
murciadb=# SELECT ts FROM generate_ts('14/10/1999 01:02:45 UTC','26/12/2000
06:01:00 UTC','3 hours') AS t(ts);
ERROR: �syntax error at or near "alias" at character 22
CONTEXT: �invalid type name "v_start alias for $1"
compile of PL/pgSQL function "generate_ts" near line 2
----------------------

Please, could some one tell me what am I doing wrong?

Thanks and best regards,

Javier
----------------------------------------------------------
El Lun 08 Mar 2004 22:47, Joe Conway escribi�:

javier garcia - CEBAS wrote:

I've loaded it in the server but when I try to use it exactly as in the
example Joe gives or in other way I obtain the answer:
---------------------
murciadb=# SELECT ts FROM generate_ts('14/10/1999 01:02:45 UTC','26/12/2000
06:01:00 UTC','3 hours') AS t(ts);
ERROR: syntax error at or near "alias" at character 22
CONTEXT: invalid type name "v_start alias for $1"
compile of PL/pgSQL function "generate_ts" near line 2

I can reproduce that message if I add a garbage character to the
beginning of the noted line. Did you retype the function or
cut-and-paste? Please run:
select prosrc from pg_proc where proname = 'generate_ts';
and post the result here.

Joe

Hi Joe;
thank a lot for the guide about the garbage character. I had copied and
pasted it before, but now I've retyped it by hand and it works perfectly.

This function will be very very useful for me.

Best regards,

Javier
-----------------------------------------------------------------
El Mar 09 Mar 2004 18:37, Joe Conway escribi�: