Re: Calculation error - Mailing list pgsql-novice

From Tom Lane
Subject Re: Calculation error
Date
Msg-id 29118.1054783045@sss.pgh.pa.us
Whole thread Raw
In response to Re: Calculation error  (chestie <mcit@argus.net.au>)
List pgsql-novice
chestie <mcit@argus.net.au> writes:
> I kept getting this same error, heres what I was trying to do.

>   it := select int4(extract(epoch from timestamp $1 -
>            extract(epoch from timestamp $2));

You should just write

  it := select int4(extract(epoch from $1) -
           extract(epoch from $2));

You are confusing the syntax
    timestamp 'string literal'
(or more generally, any type name followed by a string literal) with
something that you should apply to a non-constant value.  That syntax
works *only* for literal constants.

In your example, $1 and $2 are already of type timestamp and require
no further conversion, so the extract()s will work fine as I show above.
If you did need a run-time type conversion, you'd have to write
"CAST($1 AS timestamp)" (the SQL-spec-approved syntax) or
"$1::timestamp" (Postgres' traditional notation).

            regards, tom lane

pgsql-novice by date:

Previous
From: chestie
Date:
Subject: Re: Calculation error
Next
From: Nabil Sayegh
Date:
Subject: Re: 7.2 -> 7.3 timespan, interval etc.