Thread: arbitrary "interval" expression OK with "at time zone" but not with "set time zone" — Why?

The "at time zone" clause that can decorate a timetsamp[tz] value seems to allow an argument that’s an arbitrary
expressionthat yields a value whose data type is "interval". Here’s a contrived exotic example: 

select '2021-05-21 12:00:00 UTC'::timestamptz at time zone
  ('2015-05-21 17:00:00'::timestamp - '2015-05-21 17:00:00'::timestamp) +
  make_interval(mins=>-30) -
  '30 minutes'::interval*2;

It runs without error and gives the answer that I'd expect.

You can also supply a value whose data type is "interval" when you set the session's timezone. But you must use the
special"set time zone" syntax rather than the general "set timezone =" (or "to") syntax. This works: 

set time zone interval '-7 hours';

Moreover, the minus sign has the meaning that ordinary mortals (as opposed to native POSIX speakers) expect. That's
nice.But even this tiny spelling change: 

set time zone '-7 hours'::interval;

brings a "42601: syntax error".

The asymmetry harms usability. And it means that careful reference doc ends up voluminous, tortuous and off-putting.
Nobodylikes to have to study and remember whimsical rules that seem to have no logical justification. 

Am I failing to see that there's a logical parsing paradox that means that arbitrary "interval" expressions are
acceptableas the argument of "at time zone" but not as the argument of "set time zone"? 

Meanwhile, I'm writing a "set_timezone() procedure" with a "text" overload (that will check against a list of approved
values)and an "interval" overload that will check that the value is in a sensible range* and generate the acceptable
syntaxto execute "set time zone" dynamically. 
____________________________________________________________

* sensible range for "interval" values from this:

select '~names'   as "view",  max(utc_offset), min(utc_offset) from pg_timezone_names
union all
select '~abbrevs' as "view",  max(utc_offset), min(utc_offset) from pg_timezone_abbrevs
order by 1;

On 6/3/21 2:40 PM, Bryn Llewellyn wrote:
> The "at time zone" clause that can decorate a timetsamp[tz] value seems to allow an argument that’s an arbitrary
expressionthat yields a value whose data type is "interval". Here’s a contrived exotic example:
 
> 
> select '2021-05-21 12:00:00 UTC'::timestamptz at time zone
>    ('2015-05-21 17:00:00'::timestamp - '2015-05-21 17:00:00'::timestamp) +
>    make_interval(mins=>-30) -
>    '30 minutes'::interval*2;
> 
> It runs without error and gives the answer that I'd expect.
> 
> You can also supply a value whose data type is "interval" when you set the session's timezone. But you must use the
special"set time zone" syntax rather than the general "set timezone =" (or "to") syntax. This works:
 
> 
> set time zone interval '-7 hours';
> 
> Moreover, the minus sign has the meaning that ordinary mortals (as opposed to native POSIX speakers) expect. That's
nice.But even this tiny spelling change:
 
> 
> set time zone '-7 hours'::interval;
> 
> brings a "42601: syntax error".
> 
> The asymmetry harms usability. And it means that careful reference doc ends up voluminous, tortuous and off-putting.
Nobodylikes to have to study and remember whimsical rules that seem to have no logical justification. >
 

Not sure how the below is any of the above:

https://www.postgresql.org/docs/13/sql-set.html

"TIME ZONE

     SET TIME ZONE value is an alias for SET timezone TO value. The 
syntax SET TIME ZONE allows special syntax for the time zone 
specification. Here are examples of valid values:

     'PST8PDT'

         The time zone for Berkeley, California.
     'Europe/Rome'

         The time zone for Italy.
     -7

         The time zone 7 hours west from UTC (equivalent to PDT). 
Positive values are east from UTC.
     INTERVAL '-08:00' HOUR TO MINUTE

         The time zone 8 hours west from UTC (equivalent to PST).
     LOCAL
     DEFAULT

         Set the time zone to your local time zone (that is, the 
server's default value of timezone).

     Timezone settings given as numbers or intervals are internally 
translated to POSIX timezone syntax. For example, after SET TIME ZONE 
-7, SHOW TIME ZONE would report <-07>+07.

     See Section 8.5.3 for more information about time zones.
"
> Am I failing to see that there's a logical parsing paradox that means that arbitrary "interval" expressions are
acceptableas the argument of "at time zone" but not as the argument of "set time zone"?
 
> 
> Meanwhile, I'm writing a "set_timezone() procedure" with a "text" overload (that will check against a list of
approvedvalues) and an "interval" overload that will check that the value is in a sensible range* and generate the
acceptablesyntax to execute "set time zone" dynamically.
 
> ____________________________________________________________
> 
> * sensible range for "interval" values from this:
> 
> select '~names'   as "view",  max(utc_offset), min(utc_offset) from pg_timezone_names
> union all
> select '~abbrevs' as "view",  max(utc_offset), min(utc_offset) from pg_timezone_abbrevs
> order by 1;
> 
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Bryn Llewellyn <bryn@yugabyte.com> writes:
> The "at time zone" clause that can decorate a timetsamp[tz] value seems
> to allow an argument that’s an arbitrary expression that yields a value
> whose data type is "interval".

AT TIME ZONE is part of the SQL expression syntax, thus it's unsurprising
that its arguments can be arbitrary subexpressions.

> set time zone '-7 hours'::interval;
> brings a "42601: syntax error".

The SET statement, on the other hand, most definitely does not accept
expressions of any kind; only simple literals.  That's in line with most
other utility commands in Postgres, but there are particularly good
reasons to be wary of trying to generalize SET.  Cases such as
SET TRANSACTION ISOLATION LEVEL have to be able to execute without
a transaction context at all.

Yeah, it's asymmetric :-(.  But the SQL committee has gifted us with
a pretty asymmetric language, plus there are various implementation
constraints that are not that easy to get rid of.

            regards, tom lane

Thanks, as ever, Tom, for the quick response. I’ll summarize your explanation as “Yes, there is indeed a logical parsing paradox”. Or, as you said, as “the SQL Standard committee—the gift that keeps on giving”.

Tom wrote:

Bryn wrote:

The "at time zone" clause that can decorate a timetsamp[tz] value seems to allow an argument that’s an arbitrary expression that yields a value whose data type is "interval".

AT TIME ZONE is part of the SQL expression syntax, thus it's unsurprising that its arguments can be arbitrary subexpressions.

set time zone '-7 hours'::interval;
brings a "42601: syntax error".

The SET statement, on the other hand, most definitely does not accept expressions of any kind; only simple literals.  That's in line with most other utility commands in Postgres, but there are particularly good reasons to be wary of trying to generalize SET.  Cases such as SET TRANSACTION ISOLATION LEVEL have to be able to execute without a transaction context at all.

Yeah, it's asymmetric :-(.  But the SQL committee has gifted us with a pretty asymmetric language, plus there are various implementation constraints that are not that easy to get rid of.