[BUG?] SET TIME ZONE doesn't work with abbreviations
Hi hackers,
I noticed that `SET TIME ZONE` / `SET timezone TO` don't work with
abbreviations:
```
# select * from pg_timezone_names where abbrev = 'MSK';
name | abbrev | utc_offset | is_dst
-------------------+--------+------------+--------
Europe/Moscow | MSK | 03:00:00 | f
Europe/Simferopol | MSK | 03:00:00 | f
W-SU | MSK | 03:00:00 | f
97394 (master) =# set time zone 'Europe/Moscow';
SET
97394 (master) =# set time zone 'MSK';
ERROR: invalid value for parameter "TimeZone": "MSK"
```
However, I can use both Europe/Moscow and MSK in timestamptz_in():
```
# select '2021-09-07 12:34:56 Europe/Moscow' :: timestamptz;
timestamptz
------------------------
2021-09-07 12:34:56+03
# select '2021-09-07 12:34:56 MSK' :: timestamptz;
timestamptz
------------------------
2021-09-07 12:34:56+03
```
PostgreSQL was built on MacOS Catalina without the `--with-system-tzdata=` flag.
Is it a bug or this behavior is intentional (something to do with SQL
standard, perhaps)?
--
Best regards,
Aleksander Alekseev
On Tuesday, September 7, 2021, Aleksander Alekseev <aleksander@timescale.com>
wrote:
Hi hackers,
I noticed that `SET TIME ZONE` / `SET timezone TO` don't work with
abbreviations:Is it a bug or this behavior is intentional (something to do with SQL
standard, perhaps)?
Well, given that the limitation is documented I’d have to say it is
intentional:
You cannot set the configuration parameters TimeZone
<https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-TIMEZONE>
or log_timezone
<https://www.postgresql.org/docs/current/runtime-config-logging.html#GUC-LOG-TIMEZONE>
to
a time zone abbreviation, but you can use abbreviations in date/time input
values and with the AT TIME ZONE operator.
https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-TIMEZONES
David J.
Aleksander Alekseev <aleksander@timescale.com> writes:
I noticed that `SET TIME ZONE` / `SET timezone TO` don't work with
abbreviations:
That's intentional, per the fine manual:
A time zone abbreviation, for example <literal>PST</literal>. Such a
specification merely defines a particular offset from UTC, in
contrast to full time zone names which can imply a set of daylight
savings transition rules as well. The recognized abbreviations
are listed in the <literal>pg_timezone_abbrevs</literal> view (see <xref
linkend="view-pg-timezone-abbrevs"/>). You cannot set the
configuration parameters <xref linkend="guc-timezone"/> or
<xref linkend="guc-log-timezone"/> to a time
zone abbreviation, but you can use abbreviations in
date/time input values and with the <literal>AT TIME ZONE</literal>
operator.
I'm too caffeine-deprived to remember the exact reasoning right now,
but it was likely along the lines of "you don't really want to do
that because it won't track DST changes".
regards, tom lane