Обсуждение: [GENERAL] BST Time Zone Discrepancy

I've been having some issues with using BST as a timezone, so I checked the Postgres catalogs and found a discrepancy. 

I expected it to be British Summer Time, which is a Daylight Saving Time in England and has a 1-hour UTC offset.

In pg_timezone_abbrevs I see a 1-hr offset and is_dst set to true as I expected:

intranet=> SELECT * FROM pg_timezone_abbrevs WHERE abbrev='BST';
 abbrev | utc_offset | is_dst
--------+------------+--------
 BST    | 01:00:00   | t
(1 row)

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)

Surely, they can not both be right, so I'm thinking that this is a bug?

Thank you,

"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

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)

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/>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

Thank you, Steve and Adrian, for clarifying.

Igal Sapir
Lucee Core Developer
Lucee.org