Thread: AT TIME ZONE INTERVAL and prepared statements

AT TIME ZONE INTERVAL $1 doesn't seem to be supported in prepared
statements.

This works:

SELECT now() AT TIME ZONE INTERVAL '-08:00';

This doesn't:

PREPARE mys(text) AS SELECT now() AT TIME ZONE INTERVAL $1;
ERROR:  syntax error at or near "$1" at character 57

These work:

PREPARE mys(interval) AS SELECT now() AT TIME ZONE $1;
PREPARE mys(text) AS SELECT now() AT TIME ZONE ($1::interval)


Any reason why the second case doesn't work?

Kris Jurka

Kris Jurka <books@ejurka.com> writes:
> AT TIME ZONE INTERVAL $1 doesn't seem to be supported in prepared
> statements.

Try
    AT TIME ZONE $1
or possibly
    AT TIME ZONE $1::interval
You are confusing the syntax for a typed literal with part of the AT
TIME ZONE construct.

            regards, tom lane

On Fri, 22 Oct 2004, Tom Lane wrote:

> You are confusing the syntax for a typed literal with part of the AT
> TIME ZONE construct.

Could you explain what the difference is between a typed literal
"int '11'" and a cast(ed) literal "'11'::int" to me they seem like the
same thing.

Kris Jurka

Kris Jurka <books@ejurka.com> writes:
> On Fri, 22 Oct 2004, Tom Lane wrote:
>> You are confusing the syntax for a typed literal with part of the AT
>> TIME ZONE construct.

> Could you explain what the difference is between a typed literal
> "int '11'" and a cast(ed) literal "'11'::int" to me they seem like the
> same thing.

Yes, they are semantically the same.  The first one is a kluge that Tom
Lockhart put in to approximate the SQL spec's demands for representation
of timestamp and interval constants.  It does not work syntactically to
try to extend it to a general cast construct (we can only barely manage
to support it as-is --- there are various special cases that don't work
such as arrays and qualified type names, and a depressingly large part
of gram.y is devoted to getting the cases that do work to work).  Even
if we could get the parser to cope with that without reduce/reduce
errors, we'd be greatly reducing the grammar's ability to detect actual
syntax errors, because practically anything next to practically anything
else would become a potentially legal cast construct.

So, no "INTERVAL $1".  Bad enough we have to take "INTERVAL 'string'".
This wasn't one of the SQL committee's better ideas.

            regards, tom lane