Thread: [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

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)?

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

David, Tom,

> Well, given that the limitation is documented I’d have to say it is intentional:
> [...]

> That's intentional, per the fine manual:
> [...]

My bad, I missed this. Many thanks!

--
Best regards,
Aleksander Alekseev