Thread: PDT but not WEST
I am baffled. Both PDT and WEST appear as valid timezone abbreviations, and each have unique values, but:
test=# select timestamp with time zone '2011-09-29 18:00 PDT';
timestamptz
------------------------
2011-09-29 18:00:00-07
(1 row)
test=# select timestamp with time zone '2011-09-29 18:00 WEST';
ERROR: invalid input syntax for type timestamp with time zone: "2011-09-29 18:00 WEST"
LINE 1: select timestamp with time zone '2011-09-29 18:00 WEST';
What am I missing? Is the parser insisting on three-letter time zone abbreviations? Should it be?
--
-- Christophe Pettus
xof@thebuild.com
On 09/29/11 10:17 AM, Christophe Pettus wrote: > Both PDT and WEST appear as valid timezone abbreviations... WEST? Really? where does this appear, I've never seen that. -- john r pierce N 37, W 122 santa cruz ca mid-left coast
Christophe Pettus <xof@thebuild.com> writes:
> I am baffled. Both PDT and WEST appear as valid timezone abbreviations, and each have unique values, but:
Where do you see WEST as a valid timezone abbrevation? It's not listed
in the "Default" abbreviation list. (Perhaps it should be, since there
don't seem to be any places that don't consider it GMT+1 summer time.)
regards, tom lane
On Sep 29, 2011, at 10:50 AM, Tom Lane wrote:
> Christophe Pettus <xof@thebuild.com> writes:
>> I am baffled. Both PDT and WEST appear as valid timezone abbreviations, and each have unique values, but:
>
> Where do you see WEST as a valid timezone abbrevation?
Voila, "Western Europe Summer Time":
test=# select * from pg_timezone_names where abbrev='WEST';
name | abbrev | utc_offset | is_dst
------------------+--------+------------+--------
Atlantic/Canary | WEST | 01:00:00 | t
Atlantic/Faeroe | WEST | 01:00:00 | t
Atlantic/Faroe | WEST | 01:00:00 | t
Atlantic/Madeira | WEST | 01:00:00 | t
Europe/Lisbon | WEST | 01:00:00 | t
Portugal | WEST | 01:00:00 | t
WET | WEST | 01:00:00 | t
(7 rows)
--
-- Christophe Pettus
xof@thebuild.com
On 09/29/2011 10:50 AM, Tom Lane wrote:
> Christophe Pettus<xof@thebuild.com> writes:
>> I am baffled. Both PDT and WEST appear as valid timezone abbreviations, and each have unique values, but:
> Where do you see WEST as a valid timezone abbrevation? It's not listed
> in the "Default" abbreviation list. (Perhaps it should be, since there
> don't seem to be any places that don't consider it GMT+1 summer time.)
>
> regards, tom lane
>
select * from pg_timezone_names where abbrev = 'WEST' ;
name | abbrev | utc_offset | is_dst
------------------------+--------+------------+--------
Portugal | WEST | 01:00:00 | t
posix/Portugal | WEST | 01:00:00 | t
posix/WET | WEST | 01:00:00 | t
posix/Europe/Lisbon | WEST | 01:00:00 | t
posix/Atlantic/Canary | WEST | 01:00:00 | t
posix/Atlantic/Faeroe | WEST | 01:00:00 | t
posix/Atlantic/Madeira | WEST | 01:00:00 | t
posix/Atlantic/Faroe | WEST | 01:00:00 | t
WET | WEST | 01:00:00 | t
Europe/Lisbon | WEST | 01:00:00 | t
Atlantic/Canary | WEST | 01:00:00 | t
Atlantic/Faeroe | WEST | 01:00:00 | t
Atlantic/Madeira | WEST | 01:00:00 | t
Atlantic/Faroe | WEST | 01:00:00 | t
(14 rows)
It's interesting that there are (in my install of 9.1):
1174 distinct timezone names (all records are unique) in pg_timezone_names.
181 distinct abbreviations in pg_timezone_names
189 distinct timezone abbreviations (all unique abbreviations) in
pg_timezone_abbrevs.
But 61 abbreviations that appear in pg_timezone_names do not have a
corresponding entry in pg_timezone_abbrevs and 69 abbreviations in
pg_timezone_abbrevs that don't appear in pg_timezone_names.
There are 56 records and 3 different offsets in pg_timezone_names for
the abbreviation 'CST'.
I try to use timezone names instead of abbreviations wherever possible.
Cheers,
Steve
On 09/29/11 11:44 AM, Steve Crawford wrote: > There are 56 records and 3 different offsets in pg_timezone_names for > the abbreviation 'CST'. yeah, we had some internal java software crashing on CST when it was deployed in China :-/ I suggested the developer switch to using ISO format, and the problem was solved. -- john r pierce N 37, W 122 santa cruz ca mid-left coast
On 09/29/2011 11:44 AM, Steve Crawford wrote: > > > But 61 abbreviations that appear in pg_timezone_names do not have a > corresponding entry in pg_timezone_abbrevs and 69 abbreviations in > pg_timezone_abbrevs that don't appear in pg_timezone_names. > > Actually, given that pg_timezone_abbrevs is based on the timezone_abbreviations GUC, I'm not surprised that it is a subset of what is in pg_timezone_names. But I am a bit surprised that the opposite is true. Cheers, Steve
On Sep 29, 2011, at 11:44 AM, Steve Crawford wrote: > There are 56 records and 3 different offsets in pg_timezone_names for the abbreviation 'CST'. That's actually how this popped up for me; using 'IST' was giving rather unexpected results... -- -- Christophe Pettus xof@thebuild.com
Christophe Pettus <xof@thebuild.com> writes:
> That's actually how this popped up for me; using 'IST' was giving rather unexpected results...
IST is one of the ones where there's a real conflict, ie it means
different things to different people. That was what drove us to invent
the timezone abbreviation configuration files.
regards, tom lane
On Sep 29, 2011, at 12:11 PM, Tom Lane wrote: > IST is one of the ones where there's a real conflict, ie it means > different things to different people. Indeed; just noting that the search for a non-conflicting abbreviation is what lead me to find the WEST thing. -- -- Christophe Pettus xof@thebuild.com
Steve Crawford <scrawford@pinpointresearch.com> writes:
> Actually, given that pg_timezone_abbrevs is based on the
> timezone_abbreviations GUC, I'm not surprised that it is a subset of
> what is in pg_timezone_names. But I am a bit surprised that the opposite
> is true.
For zones that observe DST, pg_timezone_names only shows you the active
abbreviation, but pg_timezone_abbrevs includes both summer and winter
abbreviations. Also, there are some zones that have alternate
abbreviations that will never show up in pg_timezone_names
(ZULU and Z for UTC, for instance).
regards, tom lane