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;
