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

From: Bryn Llewellyn <bryn(at)yugabyte(dot)com>
To: pgsql-general list <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: arbitrary "interval" expression OK with "at time zone" but not with "set time zone" — Why?
Date: 2021-06-03 21:40:14
Message-ID: 97334D92-03D8-4066-A807-DEAC9EBF87F5@yugabyte.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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;

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Marc Millas 2021-06-03 21:43:47 Re: syntax question
Previous Message Adrian Klaver 2021-06-03 21:37:16 Re: BUG #17046: Upgrade postgres 11 to 13 version