Thread: creating sequential timestamp

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.

Thanks for your help.

Best regards,

Javier

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

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.

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:
> > 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

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ó:
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster