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:
Show quoted text
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