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". 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. Nobody likes 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 acceptable as 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 syntax 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;
