Thread: BUG #13267: Some timezones in pg_timezone_names are missing in pg_timezone_abbrevs

The following bug has been logged on the website:

Bug reference:      13267
Logged by:          Berend De Schouwer
Email address:      berend.de.schouwer@gmail.com
PostgreSQL version: 9.3.6
Operating system:   Linux Fedora 21
Description:

Some timezone abbreviations are missing in Postgresql.  For example, 'CAT'
(Central African Time)

This means that, for example, "select 'Mon May 11 10:12:54 CAT 2015'::date;"
will not work.

I'm trying to confirm "A program refuses to accept valid input (as defined
in the documentation)."  8.5.3, 48.71 and 48.72 aren't clear as to why some
abbreviations are in one table, but not the other.

'CAT' does exist in the abbrev column in pg_timezone_names; but is missing
in pg_timezone_abbrevs;

There are 30 such abbreviations missing, not counting 'GMT...':

 AQTT
 CAT
 CHOST
 ChST
 CVT
 ECT
 GST
 HDT
 HOVST
 IRDT
 MIST
 NCT
 NRT
 ORAT
 QYZT
 ROTT
 SAKT
 SAMT
 SBT
 SRET
 SRT
 SST
 SYOT
 TLT
 VOST
 WEST
 WIB
 WIT
 WITA
 WSST

berend.de.schouwer@gmail.com writes:
> Some timezone abbreviations are missing in Postgresql.  For example, 'CAT'
> (Central African Time)

CAT is not in the default abbreviation set, but if you have a need for
it, you can add it to the set of abbreviations accepted by the server.  See
http://www.postgresql.org/docs/9.3/static/datetime-config-files.html

            regards, tom lane

Berend De Schouwer <berend.de.schouwer@gmail.com> writes:
> I was just surprised to see that the list of abbrevs in _names is
> different from the list of abbrevs in _abbrevs.  I would have expected
> the lists to be identical.

Unfortunately not.  If you look a bit closer you will note that many
time zones use the very same abbreviation for entirely different UTC
offsets, so we could not possibly make those mappings be inverses.
The point of the abbreviation customization facilities is to let the
database user decide which meaning he'd like to assign to debatable
abbreviations.

Another reason for limiting what the server will accept is to reduce
the scope for errors: even though there is only one meaning for "CAT"
anywhere in the world, if you are not in Africa then it's quite likely
that such a string is a typo (perhaps for "CST", which is only one key
away) and not intended input.

> It makes for unexpected bugs when the OS, Java and the DB have different
> locale and zoneinfo datasets.

One thing you can do to cut down the variation is to use
--with-system-tzdata at configure time, so that the DB automatically uses
the OS' zoneinfo.  But I don't know of any standardized answer to the
conflicting-abbreviations problem.

            regards, tom lane

On 11/05/2015 16:37, Tom Lane wrote:
> berend.de.schouwer@gmail.com writes:
>> Some timezone abbreviations are missing in Postgresql.  For example, 'CAT'
>> (Central African Time)
> CAT is not in the default abbreviation set, but if you have a need for
> it, you can add it to the set of abbreviations accepted by the server.  See
> http://www.postgresql.org/docs/9.3/static/datetime-config-files.html
>
Thank you for the response.

We are working around the problem.  We had already decided to stop using
strings, but instead timestamp values and datetime constructs, etc.
where possible.

I was just surprised to see that the list of abbrevs in _names is
different from the list of abbrevs in _abbrevs.  I would have expected
the lists to be identical.

It makes for unexpected bugs when the OS, Java and the DB have different
locale and zoneinfo datasets.

On 11/05/2015 17:23, Tom Lane wrote:
> Berend De Schouwer <berend.de.schouwer@gmail.com> writes:
>> I was just surprised to see that the list of abbrevs in _names is
>> different from the list of abbrevs in _abbrevs.  I would have expected
>> the lists to be identical.
> Unfortunately not.  If you look a bit closer you will note that many
> time zones use the very same abbreviation for entirely different UTC
> offsets, so we could not possibly make those mappings be inverses.

That's a surprise and a bummer.  Ooh.  OK, that's a very good reason.

> Another reason for limiting what the server will accept is to reduce
> the scope for errors: even though there is only one meaning for "CAT"
> anywhere in the world, if you are not in Africa then it's quite likely
> that such a string is a typo (perhaps for "CST", which is only one key
> away) and not intended input.

That argument goes both ways :)

Anyway, the server isn't necessarily next to the client, or even on the
same continent.


Thanks for the help.