Thread: [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 Sapir
Lucee Core Developer
Lucee.org
"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 ifI 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).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 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
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.pg_timezone_names shows the *current* abbreviation for the zone in question
Thanks,
Igal Sapir
Lucee Core Developer
Lucee.org
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 ifI 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).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 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
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.pg_timezone_names shows the *current* abbreviation for the zone in questionThanks,
Igal Sapir
Lucee Core Developer
Lucee.org
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