Re: plpgsql - TIMESTAMP variables in EXTRACT - Mailing list pgsql-general

From Matthew Phillips
Subject Re: plpgsql - TIMESTAMP variables in EXTRACT
Date
Msg-id 42B093DB.6070605@timing.com
Whole thread Raw
In response to plpgsql - TIMESTAMP variables in EXTRACT  (Matthew Phillips <mphillips@timing.com>)
List pgsql-general
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
>
>




pgsql-general by date:

Previous
From: Ian Harding
Date:
Subject: Re: PostgreSQL Certification
Next
From: Simon Riggs
Date:
Subject: Re: INHERITS and planning