Thread: BUG #9374: some timzone abbver is missing from pg_timezone_abbrevs that are in pg_timezone_names

The following bug has been logged on the website:

Bug reference:      9374
Logged by:          Mark Sloan
Email address:      mark.a.sloan@gmail.com
PostgreSQL version: 9.3.3
Operating system:   CentOS 6.5
Description:

postgres=# SELECT * FROM pg_timezone_names   where abbrev = 'WIB' ;
         name         | abbrev | utc_offset | is_dst
----------------------+--------+------------+--------
 posix/Asia/Pontianak | WIB    | 07:00:00   | f
 posix/Asia/Jakarta   | WIB    | 07:00:00   | f
 Asia/Pontianak       | WIB    | 07:00:00   | f
 Asia/Jakarta         | WIB    | 07:00:00   | f
(4 rows)


postgres=# SELECT * FROM pg_timezone_abbrevs  where abbrev = 'WIB' ;
 abbrev | utc_offset | is_dst
--------+------------+--------
(0 rows)


using PGDG rpms.

postgresql-server-9.2.7, or 9.3.3
tzdata-2013i-2



I am not sure if "ALL" of these are supposed to be in pg_timezone_abbrevs.

here is the missing ones.

 abbrev
--------
 AQTT
 CAT
 CHOT
 CVT
 CWST
 ChST
 ECT
 GMT+1
 GMT+10
 GMT+11
 GMT+12
 GMT+2
 GMT+3
 GMT+4
 GMT+5
 GMT+6
 GMT+7
 GMT+8
 GMT+9
 GMT-1
 GMT-10
 GMT-11
 GMT-12
 GMT-13
 GMT-14
 GMT-2
 GMT-3
 GMT-4
 GMT-5
 GMT-6
 GMT-7
 GMT-8
 GMT-9
 GST
 HAST
 HOVT
 IRST
 MIST
 MeST
 NCT
 NRT
 ORAT
 QYZT
 ROTT
 SAKT
 SAMT
 SBT
 SRT
 SST
 SYOT
 TLT
 VOST
 WIB
 WIT
 WITA
 WSDT
 WST
(57 rows)


the WIB one is giving me grief, I am going to push that we convert to always
using timezone names rather than abbreviations, but this looked odd to me.

mark.a.sloan@gmail.com writes:
> postgres=# SELECT * FROM pg_timezone_names   where abbrev = 'WIB' ;
>          name         | abbrev | utc_offset | is_dst
> ----------------------+--------+------------+--------
>  posix/Asia/Pontianak | WIB    | 07:00:00   | f
>  posix/Asia/Jakarta   | WIB    | 07:00:00   | f
>  Asia/Pontianak       | WIB    | 07:00:00   | f
>  Asia/Jakarta         | WIB    | 07:00:00   | f
> (4 rows)

> postgres=# SELECT * FROM pg_timezone_abbrevs  where abbrev = 'WIB' ;
>  abbrev | utc_offset | is_dst
> --------+------------+--------
> (0 rows)

This is not a bug.  pg_timezone_abbrevs only lists abbreviations that are
listed in the file selected by the timezone_abbreviations parameter.
The default file doesn't contain WIB.  You can if you like add it,
or maybe better make up a custom abbreviations file tuned to your
particular needs.

While we haven't got a hard and fast policy about which timezone
abbreviations to list in the default set, there are good reasons not to
list uncommonly used ones: the more abbreviations are in the file, the
greater chance of accepting typos as valid input.  And we're definitely
going to be cautious about including abbreviations that mean different
things in different parts of the world, which is an issue for several of
the "missing" ones you listed.

            regards, tom lane

On Fri, Feb 28, 2014 at 6:57 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> mark.a.sloan@gmail.com writes:
> > postgres=# SELECT * FROM pg_timezone_names   where abbrev = 'WIB' ;
> >          name         | abbrev | utc_offset | is_dst
> > ----------------------+--------+------------+--------
> >  posix/Asia/Pontianak | WIB    | 07:00:00   | f
> >  posix/Asia/Jakarta   | WIB    | 07:00:00   | f
> >  Asia/Pontianak       | WIB    | 07:00:00   | f
> >  Asia/Jakarta         | WIB    | 07:00:00   | f
> > (4 rows)
>
> > postgres=# SELECT * FROM pg_timezone_abbrevs  where abbrev = 'WIB' ;
> >  abbrev | utc_offset | is_dst
> > --------+------------+--------
> > (0 rows)
>
> This is not a bug.  pg_timezone_abbrevs only lists abbreviations that are
> listed in the file selected by the timezone_abbreviations parameter.
> The default file doesn't contain WIB.  You can if you like add it,
> or maybe better make up a custom abbreviations file tuned to your
> particular needs

While we haven't got a hard and fast policy about which timezone
> abbreviations to list in the default set, there are good reasons not to
> list uncommonly used ones: the more abbreviations are in the file, the
> greater chance of accepting typos as valid input.  And we're definitely
> going to be cautious about including abbreviations that mean different
> things in different parts of the world, which is an issue for several of
> the "missing" ones you listed.
>
>                         regards, tom lane
>



Thank you for the clarification. I think it's going to better for the
groups I work with to just use the long name rather than the abbreviations.

-Mark