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

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

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

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.

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

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

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

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.

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs