Re: plpgsql - TIMESTAMP variables in EXTRACT

On Wed, 15 Jun 2005, Matthew Phillips wrote:

> Hi all,
>
> I have the following in a plpgsql proc on 7.3.4:
>
> <code>
> DECLARE
> ...
> curTime TIMESTAMP;
> ppsCnt INT;
>
> BEGIN
> ...
>
> -- this works
> SELECT INTO curTime localtimestamp;
>
> -- get unix seconds from current time (doesn't work)
> SELECT INTO ppsCnt EXTRACT (EPOCH FROM TIMESTAMP curTime );
> -- parser complains here ^

I think you want EXTRACT (EPOCH FROM curTime). I don't have 7.3 around,
but in 7.4 at least I was able to do something like the following:

create or replace function ff() returns int as '
declare
f timestamp(0); -- if you don''t want fractional seconds
a int;
begin
select into f localtimestamp;
select into a extract(epoch from f);
return a;
end;' language 'plpgsql';

TIMESTAMP <blah> is the syntax for a timestamp literal.