Re: [HACKERS] Timezone List
On 2006-09-17, Tom Lane [EMAIL PROTECTED] wrote: Magnus Hagander [EMAIL PROTECTED] 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 ---(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
Re: [HACKERS] Timezone List
Magnus Hagander [EMAIL PROTECTED] 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
Re: [HACKERS] Timezone List
In the CVS version there is a table with this information: http://developer.postgresql.org/pgdocs/postgres/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
Re: [HACKERS] Timezone List
Gavin Sherry [EMAIL PROTECTED] 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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Timezone List
On Thu, Sep 07, 2006 at 04:07:58AM +1000, Naz Gassiep wrote: 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. In the CVS version there is a table with this information: http://developer.postgresql.org/pgdocs/postgres/view-pg-timezonenames.html 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. Err, where does postgres use this information? I beleive there is a project on pgfoundary that has some standard datasets. Hope this helps, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [HACKERS] Timezone List
Martijn van Oosterhout wrote: In the CVS version there is a table with this information: http://developer.postgresql.org/pgdocs/postgres/view-pg-timezonenames.html Great, thanks for that Err, where does postgres use this information? I beleive there is a project on pgfoundary that has some standard datasets. Currently, it is stored in /src/timezone/data/iso3166.tab and I propose to have it available in a system view or something similar. This data is as useful as the available timezones, although I concede that it is not part of PG functionality and this may be more appropriate as a simple file that can be psql -f'd into the database if users need it as part of an app. It's more developer helper data than database functionlity and hence it could be more appropriate to distribute through the support community rather than as part of the postgresql core. Comments? ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Timezone List
Naz Gassiep [EMAIL PROTECTED] writes: Martijn van Oosterhout wrote: Err, where does postgres use this information? I beleive there is a project on pgfoundary that has some standard datasets. Currently, it is stored in /src/timezone/data/iso3166.tab and I propose to have it available in a system view or something similar. Hm. I wasn't aware that that was present in the zic database; it's not something used by or even exposed to the rest of the system. I would not support adding code that depends on it being there. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Timezone List
Martijn van Oosterhout kleptog@svana.org writes: In the CVS version there is a table with this information: http://developer.postgresql.org/pgdocs/postgres/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. 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
Re: [HACKERS] Timezone List
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 I agree with having two views, and I also think that the name as it is, is not right. I agree with pg_timezone_abbrevs and pg_timezone_names or similar. On a related note, there is not a one:one relationship between abbreviations and zone names, some abbreviations are used by two zones (forex EST, CST and others are used in Australia and the Americas) and currently it is a server configuration directive (australian_timezones) to assume Australian or American zones in the case of ambiguity. I don't know about anyone else, but the whole australian_timezones thing seems like an ugly hackaround to me. I do not have a proposed solution to this, but I see a non-trivial risk of an application being re-deployed on a server where the admin forgets to change this directive resulting in all kinds of fun and games. Forgive me if this is an already-discussed issue. I am also rather baffled at the way SAT is changed from being interpreted as a day of the week in one mode, and a timezone in another. This seems an awful incongruity of behavior, and SAT should be interpreted as a timezone in both modes. If it must be done, switching of this behavior doesn't fit in with the purpose of the australian_timezones directive and should be made the subject of a different directive (e.g., sat_is_timezone(boolean) or something similar). SAT should, IMHO, always be considered a timezone and use of the SAT string by DB programmers should be just another case for care as with any other SQL keyword. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Timezone List
Naz Gassiep [EMAIL PROTECTED] writes: I don't know about anyone else, but the whole australian_timezones thing seems like an ugly hackaround to me. You really shouldn't be pontificating about this if you haven't been paying attention to recent development work ;-) regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Timezone List
In the CVS version there is a table with this information: http://developer.postgresql.org/pgdocs/postgres/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. You know, I think I suggested that back in the days when I worked on the replacement timezone code, and you didn't want it back then ;-) If you think it's good now then yes, I still think it is. I may even have the code for it around somewhere if I go look a bit... Assuming we can sneak this in even though it's feature-freeze, want me to look for it? 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. Seems reasonable - my vote is for the more readable version. //Magnus ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Timezone List
On Thu, Sep 07, 2006 at 05:29:04AM +1000, Naz Gassiep wrote: I am also rather baffled at the way SAT is changed from being interpreted as a day of the week in one mode, and a timezone in another. Ugh. It'd be an argument if people actually used SAT as a timezone. They don't, it's ACST. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [HACKERS] Timezone List
Magnus Hagander [EMAIL PROTECTED] writes: Assuming we can sneak this in even though it's feature-freeze, want me to look for it? Yeah, please take a look --- seeing the size of the code will probably help us decide if it's too late for 8.2 or not. 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
Re: [HACKERS] Timezone List
On 2006-09-06, Tom Lane [EMAIL PROTECTED] wrote: Martijn van Oosterhout kleptog@svana.org writes: In the CVS version there is a table with this information: http://developer.postgresql.org/pgdocs/postgres/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. 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Timezone List
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/pgdocs/postgres/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. 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
