Any chance for a DB Client accessible list of allowable time zones? I've
been told that the only way to get at this list is by looking through
the source and lifting the list from zone.tab.

While I'm at it, how about an accessible list of country codes? I know
that it's not core db functionality, but these lists are so universally
useful that making users parse the files and store them in tables seems
silly.

What are other people's thoughts on this? Good idea or not?

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Martijn van Oosterhout <kleptog@svana.org> writes:
> In the CVS version there is a table with this information:
> http://developer.postgresql.org/pgdo...zonenames.html

Actually, what that view gives you is timezone offset abbreviations, not
the full zone names that you could use with SET TIME ZONE. It strikes
me that we should have a view for that as well. We could use code
similar to scan_available_timezones() to generate the view output.

It's somewhat urgent to address this now, because pg_timezonenames is
sitting on the obvious name for such a view, and once we release 8.2
we won't be able to change it. On reflection I think the existing view
is wrongly named --- perhaps it should be pg_timezoneabbrevs? Or
more readably, perhaps pg_timezone_abbrevs, with pg_timezone_names for
the other view.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

On 2006-09-06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Martijn van Oosterhout <kleptog@svana.org> writes:
>> In the CVS version there is a table with this information:
>> http://developer.postgresql.org/pgdo...zonenames.html
>
> Actually, what that view gives you is timezone offset abbreviations, not
> the full zone names that you could use with SET TIME ZONE. It strikes
> me that we should have a view for that as well. We could use code
> similar to scan_available_timezones() to generate the view output.

Any view over the full timezone names should also include the corresponding
data from zone.tab in the timezone library source.

> It's somewhat urgent to address this now, because pg_timezonenames is
> sitting on the obvious name for such a view, and once we release 8.2
> we won't be able to change it. On reflection I think the existing view
> is wrongly named --- perhaps it should be pg_timezoneabbrevs? Or
> more readably, perhaps pg_timezone_abbrevs, with pg_timezone_names for
> the other view.

Yes, the abbreviations table is definitely misnamed.

--
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services

On Wed, 6 Sep 2006, Tom Lane wrote:

> Martijn van Oosterhout <kleptog@svana.org> writes:
> > In the CVS version there is a table with this information:
> > http://developer.postgresql.org/pgdo...zonenames.html
>
> Actually, what that view gives you is timezone offset abbreviations, not
> the full zone names that you could use with SET TIME ZONE. It strikes
> me that we should have a view for that as well. We could use code
> similar to scan_available_timezones() to generate the view output.
>
> It's somewhat urgent to address this now, because pg_timezonenames is
> sitting on the obvious name for such a view, and once we release 8.2
> we won't be able to change it. On reflection I think the existing view
> is wrongly named --- perhaps it should be pg_timezoneabbrevs? Or
> more readably, perhaps pg_timezone_abbrevs, with pg_timezone_names for
> the other view.

I think 'abbrev' is a like unintuitive. How about 'short_names'?

Gavin

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

> >> In the CVS version there is a table with this information:
> >> http://developer.postgresql.org/pgdo...tgres/view-pg-
> timezonenames
> >> .html
> >
> > Actually, what that view gives you is timezone offset
> abbreviations,
> > not the full zone names that you could use with SET TIME ZONE.
> It
> > strikes me that we should have a view for that as well. We could
> use
> > code similar to scan_available_timezones() to generate the view
> output.
>
> Any view over the full timezone names should also include the
> corresponding data from zone.tab in the timezone library source.

Just noticed this mail, so that's not included in my patch. But couldn't
we just load that file up in a separate table if needed, and then join
with it when necessary?

//Magnus


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Gavin Sherry <swm@linuxworld.com.au> writes:
> On Wed, 6 Sep 2006, Tom Lane wrote:
>> It's somewhat urgent to address this now, because pg_timezonenames is
>> sitting on the obvious name for such a view, and once we release 8.2
>> we won't be able to change it. On reflection I think the existing view
>> is wrongly named --- perhaps it should be pg_timezoneabbrevs? Or
>> more readably, perhaps pg_timezone_abbrevs, with pg_timezone_names for
>> the other view.

> I think 'abbrev' is a like unintuitive. How about 'short_names'?

I'm not wedded to "abbrevs", but I don't like "short_names" because it
suggests that the names in the one view are just shorter forms of the
names in the other view, whereas really they aren't comparable things
at all (eg, EDT and EST5EDT are very different animals, because the
latter includes a set of DST transition-date rules).

I suppose the same argument could be made against "abbrevs" of course,
but it seems stronger if we have "names" and "short_names".

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

"Magnus Hagander" <mha@sollentuna.net> writes:
>> Any view over the full timezone names should also include the
>> corresponding data from zone.tab in the timezone library source.

> Just noticed this mail, so that's not included in my patch.

BTW, now that the view is in, I can't help noticing that it shows 550
different zone names, while there are only 392 entries in the zone.tab
file. I conclude that the zic people don't take maintenance of zone.tab
very seriously, and hence that we probably shouldn't rely on it.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

On 2006-09-17, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Magnus Hagander" <mha@sollentuna.net> writes:
>>> Any view over the full timezone names should also include the
>>> corresponding data from zone.tab in the timezone library source.
>
>> Just noticed this mail, so that's not included in my patch.
>
> BTW, now that the view is in, I can't help noticing that it shows 550
> different zone names, while there are only 392 entries in the zone.tab
> file. I conclude that the zic people don't take maintenance of zone.tab
> very seriously, and hence that we probably shouldn't rely on it.

You're jumping to conclusions there.

Eliminating the alias names (i.e. anything not in the form Continent/*),
we get only 45 differences, of which 40 are backward-compatibility aliases
(see the "backward" source file). The remaining five are:

Asia/Riyadh87
Asia/Riyadh88
Asia/Riyadh89 (the three Riyadh?? zones are local solar time for specific
years)
Asia/Istanbul (alias for Europe/Istanbul)
Europe/Nicosia (alias for Asia/Nicosia)

So the list in zone.tab _is_ complete, it just doesn't list aliases (which
it isn't supposed to). The reason to include zone.tab in with the data
(unlike the current setup which doesn't bother to even install the file
anywhere) is to provide an answer to the question "what timezone(s) are
applicable to a specific country". For that purpose aliases are irrelevent.

--
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services