Postgres Pro
Downloads
Home   >   mailing lists

Thread: Extract Function

Extract Function

From
"Derrick Betts"
Date:
I am trying to use the EXTRACT function in pl/pgsql and running into a problem.
 
This is the command:
 
SELECT EXTRACT(EPOCH FROM TIMESTAMP variable_name) INTO Temp;
 
The variable_name is of type text (I've also tried it as type timestamp), but the parser breaks when it attempts to perform this function.  It works great when I substitute the variable_name with '2004-1-10 00:00:00' , however, when I use a variable, it doesn't.
 
Any one have any ideas on how I can get variable substitution to work in my function using EXTRACT?
 
Thanks,
Derrick
 

Re: Extract Function

From
Stephan Szabo
Date:
On Fri, 19 Mar 2004, Derrick Betts wrote:

> I am trying to use the EXTRACT function in pl/pgsql and running into a problem.
>
> This is the command:
>
> SELECT EXTRACT(EPOCH FROM TIMESTAMP variable_name) INTO Temp;
>
> The variable_name is of type text (I've also tried it as type
> timestamp), but the parser breaks when it attempts to perform this
> function.  It works great when I substitute the variable_name with
> '2004-1-10 00:00:00' , however, when I use a variable, it doesn't.

TIMESTAMP <foo> is meant for timestamp literals.  If you want to
convert a value from one type to another, you probably want something
like CAST(variable_name AS TIMESTAMP)

Re: Extract Function

From
Bruno Wolff III
Date:
On Fri, Mar 19, 2004 at 07:44:25 -0700,
  Derrick Betts <Derrick@Blue-Axis.com> wrote:
> I am trying to use the EXTRACT function in pl/pgsql and running into a problem.
>
> This is the command:
>
> SELECT EXTRACT(EPOCH FROM TIMESTAMP variable_name) INTO Temp;
>
> The variable_name is of type text (I've also tried it as type timestamp), but the parser breaks when it attempts to
performthis function.  It works great when I substitute the variable_name with '2004-1-10 00:00:00' , however, when I
usea variable, it doesn't. 
>
> Any one have any ideas on how I can get variable substitution to work in my function using EXTRACT?

I don't think typename variable is a valid operation. You would need to use
CAST or :: to do a typecast. Probably you should just leave out "TIMESTAMP"
from your example.

Re: Extract Function

From
Tom Lane
Date:
"Derrick Betts" <Derrick@Blue-Axis.com> writes:
> This is the command:
> SELECT EXTRACT(EPOCH FROM TIMESTAMP variable_name) INTO Temp;

You're confusing the EXTRACT function with the notation for a literal
constant of a specific type.  The function is just

    EXTRACT(EPOCH FROM timestamp-expression)

An example that involves a literal constant could be written either

    EXTRACT(EPOCH FROM TIMESTAMP '2004-1-10 00:00:00')
    EXTRACT(EPOCH FROM '2004-1-10 00:00:00'::TIMESTAMP)

but when you are dealing with a variable you don't use the TIMESTAMP
decoration, because the parser already knows what datatype the variable
is.  So

    EXTRACT(EPOCH FROM timestamp-variable)

            regards, tom lane