Thread: plpgsql - TIMESTAMP variables in EXTRACT
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 ^ -- get unix seconds from random string (works) SELECT INTO ppsCnt EXTRACT (EPOCH FROM TIMESTAMP \'1990-05-04 01:02:03\' ); -- do stuff with ppsCnt and curTime ... </code> Here are the error messages: WARNING: Error occurred while executing PL/pgSQL function XXX WARNING: line XXX at select into variables ERROR: parser: parse error at or near "$1" at character XX All I would like to do is get the current time and convert it to an integer value. Am I attempting to do the impossible or is this another simpler way to shove unix seconds of the current time in a variable? Please also inform me as to why my current syntax is invalid. Thanks, Matthew
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.
Stephan, Thanks, this does work. I assume that the usage of 'TIMESTAMP' only applies when a literal representation of the date is given. Matthew Stephan Szabo wrote: > 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. > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > >