arbitrary "interval" expression OK with "at time zone" but not with "set time zone" — Why?
От | Bryn Llewellyn |
---|---|
Тема | arbitrary "interval" expression OK with "at time zone" but not with "set time zone" — Why? |
Дата | |
Msg-id | 97334D92-03D8-4066-A807-DEAC9EBF87F5@yugabyte.com обсуждение исходный текст |
Ответы |
Re: arbitrary "interval" expression OK with "at time zone" but not with "set time zone" — Why?
Re: arbitrary "interval" expression OK with "at time zone" but not with "set time zone" — Why? |
Список | pgsql-general |
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;
В списке pgsql-general по дате отправления: