BST Time Zone Discrepancy

"Igal @ Lucee.org" <igal@lucee.org> writes:

I've been having some issues with using BST as a timezone, so I checked
the Postgres catalogs and found a discrepancy.
...
But in pg_timezone_names I get an 11-hr offset with a completely
different time zone.

intranet=> SELECT * FROM pg_timezone_names WHERE abbrev='BST';
name | abbrev | utc_offset | is_dst
----------------------------+--------+------------+--------
Pacific/Bougainville | BST | 11:00:00 | f
posix/Pacific/Bougainville | BST | 11:00:00 | f
(2 rows)

pg_timezone_names shows the *current* abbreviation for the zone in
question, so right now I get:

regression=# select * from pg_timezone_names where name like '%London';
name | abbrev | utc_offset | is_dst
---------------------+--------+------------+--------
posix/Europe/London | GMT | 00:00:00 | f
Europe/London | GMT | 00:00:00 | f
(2 rows)

I don't see any particular problem here. If you write 'BST' in an input
value, it will be understood as GMT+1, as you're expecting.

This is controlled by the timezone_abbreviations file, which if
you haven't changed it lists:

# CONFLICT! BST is not unique
# Other timezones:
# - BST: Bougainville Standard Time (Papua New Guinea)
BST 3600 D # British Summer Time
# (Europe/London)

regards, tom lane

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

Tom,

Thank you for your reply:

On 2/6/2017 12:18 PM, Tom Lane wrote:

This is controlled by the timezone_abbreviations file, which if
you haven't changed it lists:

# CONFLICT! BST is not unique
# Other timezones:
# - BST: Bougainville Standard Time (Papua New Guinea)
BST 3600 D # British Summer Time
# (Europe/London)

I haven't changed any of the config files. I can not find that file on
my system (maybe it's in the source code only).

I am using the Red Hat distribution: PostgreSQL 9.6.1 on
x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat
4.8.5-4), 64-bit

pg_timezone_names shows the*current* abbreviation for the zone in question

I'm not sure what you mean by "current". If this is not an issue then
that's fine, you can ignore this message. It just seemed weird to me
that pg_timezone_names and pg_timezone_abbrevs showed very different
results for the same code.

Thanks,

Igal Sapir
Lucee Core Developer
Lucee.org <http://lucee.org/&gt;

On Mon, Feb 6, 2017 at 12:44 PM, Igal @ Lucee.org <igal@lucee.org> wrote:

Tom,

Thank you for your reply:
On 2/6/2017 12:18 PM, Tom Lane wrote:

This is controlled by the timezone_abbreviations file, which if

you haven't changed it lists:

# CONFLICT! BST is not unique
# Other timezones:
# - BST: Bougainville Standard Time (Papua New Guinea)
BST 3600 D # British Summer Time
# (Europe/London)

I haven't changed any of the config files. I can not find that file on my
system (maybe it's in the source code only).

I am using the Red Hat distribution: PostgreSQL 9.6.1 on
x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat
4.8.5-4), 64-bit

pg_timezone_names shows the **current** abbreviation for the zone in question

I'm not sure what you mean by "current". If this is not an issue then
that's fine, you can ignore this message. It just seemed weird to me that
pg_timezone_names and pg_timezone_abbrevs showed very different results for
the same code.

Thanks,

Igal Sapir
Lucee Core Developer
Lucee.org <http://lucee.org/&gt;

"Current" in this context means the abbreviation in effect at the current
time. In other words, if I were to look at my current time zone
abbreviation it would currently be PST (Pacific Standard time) but in a
couple months it would be PDT so the *view* will change depending on the
time of year.

It's important to note that there is no BST timezone. That is an
abbreviation for a timezone *offset*. Where I live we could be in PST (-08)
or PDT (-07). Currently we are in standard time but I could ask for the
current time in daylight time and PostgreSQL will give me the current point
in time with a PDT or -07 offset. Compounding the problem is the fact that
abbreviations are not globally unique. Both the United States and Australia
have Eastern and Central Standard and Daylight times, for example.

A timezone, on the other hand, encapsulates the offset as it changes both
throughout the year and historically. It is almost always preferable to use
an actual timezone by specifying it by name as in Europe/London,
America/Los_Angeles, etc.

Cheers,
Steve

On 02/06/2017 12:44 PM, Igal @ Lucee.org wrote:

Tom,

Thank you for your reply:

On 2/6/2017 12:18 PM, Tom Lane wrote:

This is controlled by the timezone_abbreviations file, which if
you haven't changed it lists:

# CONFLICT! BST is not unique
# Other timezones:
# - BST: Bougainville Standard Time (Papua New Guinea)
BST 3600 D # British Summer Time
# (Europe/London)

I haven't changed any of the config files. I can not find that file on
my system (maybe it's in the source code only).

timezone_abbreviations is actually a setting in postgresql.conf. The
file Tom is referring to is the file that setting points to, by default
that is Default. This is a file in the Postgres share/timezonesets
directory. A do not use RH so I am not sure where that directory lives.

I am using the Red Hat distribution: PostgreSQL 9.6.1 on
x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat
4.8.5-4), 64-bit

pg_timezone_names shows the *current* abbreviation for the zone in question

I'm not sure what you mean by "current". If this is not an issue then
that's fine, you can ignore this message. It just seemed weird to me
that pg_timezone_names and pg_timezone_abbrevs showed very different
results for the same code.

Thanks,

Igal Sapir
Lucee Core Developer
Lucee.org <http://lucee.org/&gt;

--
Adrian Klaver
adrian.klaver@aklaver.com

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

Thank you, Steve and Adrian, for clarifying.

Igal Sapir
Lucee Core Developer
Lucee.org <http://lucee.org/&gt;