Thread: Timezone view

I brought this up a while ago, but I didn't get any responses, I assume 
due to everyone being too busy with 8.3

I think that it would be great if the pg_timezone_names and 
pg_timezone_abbrevs included a boolean field indicating if that item is 
in the Olsen DB or if it is a system alias or other added item. This 
would make it far easier to integrate the data in the view with external 
data sources that also use the Olsen DB.

It may also be beneficial to add the ISO 3166 column into that view, the 
data is in zone.tab and I can't see a reason to not include it.

- Naz.

Naz Gassiep <naz@mira.net> writes:
> I think that it would be great if the pg_timezone_names and 
> pg_timezone_abbrevs included a boolean field indicating if that item is 
> in the Olsen DB

Huh?  They're all in the Olsen DB.
        regards, tom lane

Naz Gassiep wrote:

> It may also be beneficial to add the ISO 3166 column into that view, the  
> data is in zone.tab and I can't see a reason to not include it.

We also have the country name in iso3166.tab and the geo coordinates.
And there is also a comment field.  Which ones make sense to add?

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Tom Lane wrote:
> Naz Gassiep <naz@mira.net> writes:
>   
>> I think that it would be great if the pg_timezone_names and 
>> pg_timezone_abbrevs included a boolean field indicating if that item is 
>> in the Olsen DB
>>     
>
> Huh?  They're all in the Olsen DB
Not true, the zone.tab file has 398 zones defined, and in my PG 8.2 
running on Debian, there are 564 timezones listed in pg_timezone_names. 
The field I propose would indicate which 398 of those 564 are listed in 
zone.tab as those are the actual Olsen defined timezones, the rest are 
aliases.

Regards,
- Naz.

Alvaro Herrera wrote:
> Naz Gassiep wrote:
>
>   
>> It may also be beneficial to add the ISO 3166 column into that view, the  
>> data is in zone.tab and I can't see a reason to not include it.
>>     
>
> We also have the country name in iso3166.tab and the geo coordinates.
> And there is also a comment field.  Which ones make sense to add

I'd say the country name and geo coordinates both make sense to add.

Regards,
- Naz.

On Wed, Feb 20, 2008 at 11:43:43PM +0700, Naz Gassiep wrote:
> Not true, the zone.tab file has 398 zones defined, and in my PG 8.2
> running on Debian, there are 564 timezones listed in pg_timezone_names.
> The field I propose would indicate which 398 of those 564 are listed in
> zone.tab as those are the actual Olsen defined timezones, the rest are
> aliases.

The zone.tab doesn't have all the zones. Try:

find /usr/share/zoneinfo/ -name posix -prune -o -name right -prune -o -type f |wc -l

Gives 577 on my system.

Not sure if the zone.tab is even used.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Those who make peaceful revolution impossible will make violent revolution inevitable.
>  -- John F Kennedy