Timezone List
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?
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/
Show quoted text
From each according to his ability. To each according to his ability to litigate.
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?
Naz Gassiep <naz@mira.net> 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
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
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.
Naz Gassiep <naz@mira.net> 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
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
Aah, sorry, I don't watch dev progress very closely. This is something
that has been bugging me since 7.4.x branch and I never got around to
whining about it. Consider my comments retracted.
- Naz
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
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/
Show quoted text
From each according to his ability. To each according to his ability to litigate.
"Magnus Hagander" <mha@sollentuna.net> 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
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/pgdocs/postgres/view-pg-timezonenames.htmlActually, 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/pgdocs/postgres/view-pg-timezonenames.htmlActually, 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
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.
Here goes. Tested only on win32 so far, but works there. No docs yet
either - need to know if it goes in first ;)
It only returns the name, but if you want more funky stuff you can do
things like
select name,current_timestamp at time zone name from pg_timezone_names
//Magnus
Attachments:
pg_timezone_names.diffapplication/octet-stream; name=pg_timezone_names.diffDownload
Index: src/backend/catalog/system_views.sql
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/catalog/system_views.sql,v
retrieving revision 1.30
diff -c -r1.30 system_views.sql
*** src/backend/catalog/system_views.sql 19 Aug 2006 01:36:24 -0000 1.30
--- src/backend/catalog/system_views.sql 7 Sep 2006 12:27:50 -0000
***************
*** 186,196 ****
GRANT SELECT, UPDATE ON pg_settings TO PUBLIC;
! CREATE VIEW pg_timezonenames AS
SELECT *
! FROM pg_timezonenames() AS T
(name text, utc_offset interval, is_dst boolean);
-- Statistics views
CREATE VIEW pg_stat_all_tables AS
--- 186,201 ----
GRANT SELECT, UPDATE ON pg_settings TO PUBLIC;
! CREATE VIEW pg_timezone_abbrevs AS
SELECT *
! FROM pg_timezone_abbrevs() AS T
(name text, utc_offset interval, is_dst boolean);
+ CREATE VIEW pg_timezone_names AS
+ SELECT *
+ FROM pg_timezone_names() AS T
+ (name text);
+
-- Statistics views
CREATE VIEW pg_stat_all_tables AS
Index: src/backend/utils/adt/datetime.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/utils/adt/datetime.c,v
retrieving revision 1.170
diff -c -r1.170 datetime.c
*** src/backend/utils/adt/datetime.c 4 Sep 2006 01:26:27 -0000 1.170
--- src/backend/utils/adt/datetime.c 7 Sep 2006 12:27:53 -0000
***************
*** 3842,3848 ****
* and returns a set of (name, utc_offset, is_dst).
*/
Datum
! pg_timezonenames(PG_FUNCTION_ARGS)
{
FuncCallContext *funcctx;
int *pindex;
--- 3842,3848 ----
* and returns a set of (name, utc_offset, is_dst).
*/
Datum
! pg_timezone_abbrevs(PG_FUNCTION_ARGS)
{
FuncCallContext *funcctx;
int *pindex;
***************
*** 3877,3883 ****
/*
* build tupdesc for result tuples. This must match the
! * definition of the pg_timezonenames view in system_views.sql
*/
tupdesc = CreateTemplateTupleDesc(3, false);
TupleDescInitEntry(tupdesc, (AttrNumber) 1, "name",
--- 3877,3883 ----
/*
* build tupdesc for result tuples. This must match the
! * definition of the pg_timezone_names view in system_views.sql
*/
tupdesc = CreateTemplateTupleDesc(3, false);
TupleDescInitEntry(tupdesc, (AttrNumber) 1, "name",
***************
*** 3928,3930 ****
--- 3928,3995 ----
SRF_RETURN_NEXT(funcctx, result);
}
+
+ /*
+ * This set-returning function reads all the available time zone names
+ */
+ Datum
+ pg_timezone_names(PG_FUNCTION_ARGS)
+ {
+ MemoryContext oldcontext;
+ FuncCallContext *funcctx;
+ pg_tzenum *tzenum;
+ pg_tz *tz;
+ Datum result;
+ HeapTuple tuple;
+ Datum values[1];
+ bool nulls[1];
+
+ /* stuff done only on the first call of the function */
+ if (SRF_IS_FIRSTCALL())
+ {
+ TupleDesc tupdesc;
+
+ /* create a function context for cross-call persistence */
+ funcctx = SRF_FIRSTCALL_INIT();
+
+ /*
+ * switch to memory context appropriate for multiple function
+ * calls
+ */
+ oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
+
+ /* allocate memory for user context */
+ tzenum = pg_tzenumerate_start();
+ funcctx->user_fctx = (void *) tzenum;
+
+ /*
+ * build tupdesc for result tuples. This must match the
+ * definition of the pg_timezone_abbrevs view in system_views.sql
+ */
+ tupdesc = CreateTemplateTupleDesc(1, false);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 1, "name",
+ TEXTOID, -1, 0);
+
+ funcctx->tuple_desc = BlessTupleDesc(tupdesc);
+ MemoryContextSwitchTo(oldcontext);
+ }
+
+ /* stuff done on every call of the function */
+ funcctx = SRF_PERCALL_SETUP();
+ tzenum = (pg_tzenum *) funcctx->user_fctx;
+
+ oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
+ tz = pg_tzenumerate_next(tzenum);
+ MemoryContextSwitchTo(oldcontext);
+ if (!tz)
+ SRF_RETURN_DONE(funcctx);
+
+ MemSet(nulls, 0, sizeof(nulls));
+
+ values[0] = DirectFunctionCall1(textin, CStringGetDatum(pg_get_timezone_name(tz)));
+
+ tuple = heap_form_tuple(funcctx->tuple_desc, values, nulls);
+ result = HeapTupleGetDatum(tuple);
+
+ SRF_RETURN_NEXT(funcctx, result);
+ }
Index: src/include/pgtime.h
===================================================================
RCS file: /projects/cvsroot/pgsql/src/include/pgtime.h,v
retrieving revision 1.12
diff -c -r1.12 pgtime.h
*** src/include/pgtime.h 5 Mar 2006 15:58:53 -0000 1.12
--- src/include/pgtime.h 7 Sep 2006 12:27:56 -0000
***************
*** 38,43 ****
--- 38,44 ----
};
typedef struct pg_tz pg_tz;
+ typedef struct pg_tzenum pg_tzenum;
extern struct pg_tm *pg_localtime(const pg_time_t *timep, const pg_tz *tz);
extern struct pg_tm *pg_gmtime(const pg_time_t *timep);
***************
*** 56,61 ****
--- 57,66 ----
extern bool tz_acceptable(pg_tz *tz);
extern const char *pg_get_timezone_name(pg_tz *tz);
+ extern pg_tzenum *pg_tzenumerate_start(void);
+ extern pg_tz *pg_tzenumerate_next(pg_tzenum *dir);
+ extern void pg_tzenumerate_end(pg_tzenum *dir);
+
extern pg_tz *global_timezone;
/* Maximum length of a timezone name (not including trailing null) */
Index: src/include/catalog/pg_proc.h
===================================================================
RCS file: /projects/cvsroot/pgsql/src/include/catalog/pg_proc.h,v
retrieving revision 1.422
diff -c -r1.422 pg_proc.h
*** src/include/catalog/pg_proc.h 19 Aug 2006 01:36:33 -0000 1.422
--- src/include/catalog/pg_proc.h 7 Sep 2006 12:27:58 -0000
***************
*** 3787,3793 ****
DESCR("get the prepared statements for this session");
DATA(insert OID = 2511 ( pg_cursor PGNSP PGUID 12 f f t t s 0 2249 "" _null_ _null_ _null_ pg_cursor - _null_ ));
DESCR("get the open cursors for this session");
! DATA(insert OID = 2599 ( pg_timezonenames PGNSP PGUID 12 f f t t s 0 2249 "" _null_ _null_ _null_ pg_timezonenames - _null_ ));
DESCR("get the available time zone names");
/* non-persistent series generator */
--- 3787,3795 ----
DESCR("get the prepared statements for this session");
DATA(insert OID = 2511 ( pg_cursor PGNSP PGUID 12 f f t t s 0 2249 "" _null_ _null_ _null_ pg_cursor - _null_ ));
DESCR("get the open cursors for this session");
! DATA(insert OID = 2599 ( pg_timezone_abbrevs PGNSP PGUID 12 f f t t s 0 2249 "" _null_ _null_ _null_ pg_timezone_abbrevs - _null_ ));
! DESCR("get the available time zone abbreviations");
! DATA(insert OID = 2600 ( pg_timezone_names PGNSP PGUID 12 f f t t s 0 2249 "" _null_ _null_ _null_ pg_timezone_names - _null_ ));
DESCR("get the available time zone names");
/* non-persistent series generator */
Index: src/include/utils/datetime.h
===================================================================
RCS file: /projects/cvsroot/pgsql/src/include/utils/datetime.h,v
retrieving revision 1.60
diff -c -r1.60 datetime.h
*** src/include/utils/datetime.h 25 Jul 2006 03:51:22 -0000 1.60
--- src/include/utils/datetime.h 7 Sep 2006 12:27:59 -0000
***************
*** 303,308 ****
extern bool CheckDateTokenTables(void);
extern void InstallTimeZoneAbbrevs(tzEntry *abbrevs, int n);
! extern Datum pg_timezonenames(PG_FUNCTION_ARGS);
#endif /* DATETIME_H */
--- 303,309 ----
extern bool CheckDateTokenTables(void);
extern void InstallTimeZoneAbbrevs(tzEntry *abbrevs, int n);
! extern Datum pg_timezone_abbrevs(PG_FUNCTION_ARGS);
! extern Datum pg_timezone_names(PG_FUNCTION_ARGS);
#endif /* DATETIME_H */
Index: src/timezone/pgtz.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/timezone/pgtz.c,v
retrieving revision 1.44
diff -c -r1.44 pgtz.c
*** src/timezone/pgtz.c 14 Jul 2006 14:52:27 -0000 1.44
--- src/timezone/pgtz.c 7 Sep 2006 12:28:04 -0000
***************
*** 1136,1138 ****
--- 1136,1246 ----
SetConfigOption("timezone", def_tz, PGC_POSTMASTER, PGC_S_ARGV);
}
}
+
+
+ /*
+ * Functions to enumerate available timezones
+ *
+ * Note that pg_tzenumerate_next() will return a pointer into the pg_tzenum structure,
+ * so the data is only valid up to the next call.
+ *
+ * All data is allocated using palloc in the current context.
+ */
+ #define MAX_TZDIR_DEPTH 10
+ struct pg_tzenum {
+ int baselen;
+ int depth;
+ DIR *dirdesc[MAX_TZDIR_DEPTH];
+ char *dirname[MAX_TZDIR_DEPTH];
+ struct pg_tz tz;
+ } ;
+
+ pg_tzenum *
+ pg_tzenumerate_start(void)
+ {
+ struct pg_tzenum *ret = palloc(sizeof(struct pg_tzenum));
+
+ ret->baselen = strlen(pg_TZDIR())+1;
+ ret->depth = 0;
+ ret->dirname[0] = pstrdup(pg_TZDIR());
+ ret->dirdesc[0] = AllocateDir(pg_TZDIR());
+ if (!ret->dirdesc[0])
+ ereport(ERROR,
+ (errcode_for_file_access(),
+ errmsg("could not open directory \"%s\": %m", pg_TZDIR())));
+
+ return ret;
+ }
+
+ void
+ pg_tzenumerate_end(pg_tzenum *dir)
+ {
+ while (dir->depth >= 0)
+ {
+ FreeDir(dir->dirdesc[dir->depth]);
+ pfree(dir->dirdesc[dir->depth]);
+ dir->depth--;
+ }
+ pfree(dir);
+ }
+
+ pg_tz *
+ pg_tzenumerate_next(pg_tzenum *dir)
+ {
+ while (dir->depth >= 0)
+ {
+ struct dirent *direntry;
+ char fullname[MAXPGPATH];
+ struct stat statbuf;
+
+ direntry = ReadDir(dir->dirdesc[dir->depth], dir->dirname[dir->depth]);
+
+ if (!direntry)
+ {
+ /* End of this directory */
+ FreeDir(dir->dirdesc[dir->depth]);
+ dir->depth--;
+ continue;
+ }
+
+ if (direntry->d_name[0] == '.')
+ continue;
+
+ snprintf(fullname, MAXPGPATH, "%s/%s", dir->dirname[dir->depth], direntry->d_name);
+ if (stat(fullname, &statbuf) != 0)
+ ereport(ERROR,
+ (errcode_for_file_access(),
+ errmsg("could not stat \"%s\": %m", fullname)));
+
+ if (S_ISDIR(statbuf.st_mode))
+ {
+ /* Step into the subdirectory */
+ if (dir->depth == MAX_TZDIR_DEPTH-1)
+ ereport(ERROR,
+ (errmsg("timezone directory stack overflow")));
+ dir->depth++;
+ dir->dirname[dir->depth] = pstrdup(fullname);
+ dir->dirdesc[dir->depth] = AllocateDir(fullname);
+ if (!dir->dirdesc[dir->depth])
+ ereport(ERROR,
+ (errcode_for_file_access(),
+ errmsg("could not open directory \"%s\": %m", fullname)));
+
+ /* Start over reading in the new directory */
+ continue;
+ }
+
+ /* Load this timezone using tzload() not pg_tzset(), so we don't fill the cache */
+ if (tzload(fullname + dir->baselen, &dir->tz.state) != 0)
+ /* Zone could not be loaded, and we don't do parsing. Try next. */
+ continue;
+
+ /* Timezone loaded OK. */
+ strcpy(dir->tz.TZname, fullname + dir->baselen);
+ return &dir->tz;
+ }
+
+ /* Nothing more found */
+ return NULL;
+ }
+
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.
Here goes. Tested only on win32 so far, but works there. No docs
yet either - need to know if it goes in first ;)It only returns the name, but if you want more funky stuff you can
do things likeselect name,current_timestamp at time zone name from
pg_timezone_names
Oh, and it's going to need an update to expected/rules.out, not included
in the patch, as well.
//Magnus
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
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
"Magnus Hagander" <mha@sollentuna.net> 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.
Here goes. Tested only on win32 so far, but works there. No docs yet
either - need to know if it goes in first ;)
I've applied this along with some extra work to get it to show GMT
offsets and DST status, which should be useful for helping people
to choose which setting they want. This effectively obsoletes
Table B-5 as well as B-4 in the SGML docs ... we should probably
remove both of those in favor of recommending people look at the
views.
I did the basic documentation work in catalogs.sgml for these views,
but Appendix B still needs an update. Joachim, you were going to do
that, right?
regards, tom lane
"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
On Sat, Sep 16, 2006 at 04:19:48PM -0400, Tom Lane wrote:
I've applied this along with some extra work to get it to show GMT
offsets and DST status, which should be useful for helping people
to choose which setting they want. This effectively obsoletes
Table B-5 as well as B-4 in the SGML docs ... we should probably
remove both of those in favor of recommending people look at the
views.
I did the basic documentation work in catalogs.sgml for these views,
but Appendix B still needs an update. Joachim, you were going to do
that, right?
Thats right.
Joachim
--
Joachim Wieland joe@mcknight.de
GPG key available
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
On Sat, Sep 16, 2006 at 04:19:48PM -0400, Tom Lane wrote:
"Magnus Hagander" <mha@sollentuna.net> writes:
Here goes. Tested only on win32 so far, but works there. No docs yet
either - need to know if it goes in first ;)
I've applied this along with some extra work to get it to show GMT
offsets and DST status, which should be useful for helping people
to choose which setting they want. This effectively obsoletes
Table B-5 as well as B-4 in the SGML docs ... we should probably
remove both of those in favor of recommending people look at the
views.
http://momjian.us/main/writings/pgsql/sgml/view-pg-timezone-names.html says
that the names in the view are "recognized" as argument to "SET TIMEZONE".
However some of them can still not be used if they contain leap seconds, try
for example
set timezone to 'Mideast/Riyadh87';
Should we just document that some can't be set or remove those from the view
completely or add another boolean column has_leapsecs or similar?
Removing them seems not to be the right idea because you can say:
select now() at time zone 'Mideast/Riyadh87';
Joachim
--
Joachim Wieland joe@mcknight.de
GPG key available