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;
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 |