pg.ddx.io pgsql-interfaces@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Timezone database questions 5+ messages / 2 participants [nested] [flat]
* Timezone database questions @ 2004-05-02 11:11 Bruce Momjian <pgman@candle.pha.pa.us> 0 siblings, 0 replies; 5+ messages in thread From: Bruce Momjian @ 2004-05-02 11:11 UTC (permalink / raw) To: PostgreSQL-development <pgsql-hackers@postgreSQL.org>; +Cc: pgsql-interfaces; PostgreSQL Win32 port list <pgsql-hackers-win32@postgreSQL.org> I have added a timezone database to CVS, and enabled it for Win32. This allows Win32 to pass our pre-1970 regression tests. There are also plans to enable this code under Unix so we have a standard database for all installs and so we can query for valid timezone names. However, this brings up some questions: 1) How do we set the default local timezone for our database? The OS knows the local timezone. How do we set our local timezone on Win32? On Unix? (On Unix, there is usually an /etc/localtime file that is created during install.) Perhaps we can query the current timezone specification (e.g. EDT), and do some kind of lookup. I know of no way to get the full specification, e.g. EST5EDT or America/New_York. 2) Does ecpg need to use our database or can it use the local one? If it uses ours, it adds a requirement that all ecpg executables need access to our database in a compile-time-defined fixed directory. (Yuck.) If it does not, is it OK that there is a mismatch? I am sure we had this issue before because you could run clients and servers on different OS's, but when everything was on the same OS, there was no mismatch, while if ecpg uses the local OS, there will be a mismatch where there wasn't one before. 3) Should we move the timezone source code from src/timezone to src/backend/timezone if only the backend is using the timezone database. 4) The timezone data files are installed in /pgsql/share/timezone. Is that OK? 5) We only had a compiled-in location for /lib in the past for dynamic loading, and had a GUC variable to override it. initdb always used /share in a fixed location, but it has a flag to override it. With /share/timezone, the server now requires the timezone database to be in a fixed location too. Do we now need a sharedir GUC variable? 6) Can someone get this code working on Unix. I get the right value for timeofday(), but now() and CURRENT_TIMESTAMP return wrong and different values. 7) Why can't we just test for valid timezones by setting the timezone string and checking that the timezone returned isn't GMT: $ TZ="abc" date Sun May 2 11:02:26 GMT 2004 As you can see, our own timezone database has brought perhaps more problems than it will solve. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073 ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Timezone database questions @ 2004-05-02 11:55 Magnus Hagander <mha@sollentuna.net> 2004-05-02 12:07 ` Re: Timezone database questions Bruce Momjian <pgman@candle.pha.pa.us> 0 siblings, 1 reply; 5+ messages in thread From: Magnus Hagander @ 2004-05-02 11:55 UTC (permalink / raw) To: Bruce Momjian <pgman@candle.pha.pa.us>; PostgreSQL-development <pgsql-hackers@postgreSQL.org>; +Cc: pgsql-interfaces; PostgreSQL Win32 port list <pgsql-hackers-win32@postgreSQL.org> >I have added a timezone database to CVS, and enabled it for >Win32. This >allows Win32 to pass our pre-1970 regression tests. There are also >plans to enable this code under Unix so we have a standard database for >all installs and so we can query for valid timezone names. > >However, this brings up some questions: > >1) How do we set the default local timezone for our database? The OS >knows the local timezone. How do we set our local timezone on Win32? >On Unix? (On Unix, there is usually an /etc/localtime file that is >created during install.) Perhaps we can query the current timezone >specification (e.g. EDT), and do some kind of lookup. I know of no way >to get the full specification, e.g. EST5EDT or America/New_York. Could we just require that it is configured through postgresql.conf? IIRC there is a parameter to set it there. IMO, that would be enough. If not, we can use GetTimeZoneInformation(). It returns the standard name (amongst other things) of the tz configured in the system. Or do nothing. On unix it defaults to the value of the TZ environment variables. If you set that one before you start it, you get the same behaviour. In that case, I'd still say we *recommend* setting it in postgresql.conf, but it will still work. >2) Does ecpg need to use our database or can it use the local one? If >it uses ours, it adds a requirement that all ecpg executables need >access to our database in a compile-time-defined fixed directory. >(Yuck.) If it does not, is it OK that there is a mismatch? I am sure >we had this issue before because you could run clients and servers on >different OS's, but when everything was on the same OS, there was no >mismatch, while if ecpg uses the local OS, there will be a mismatch >where there wasn't one before. As I've said before, I don't hink ecpg should use the same database. And AFAIK the TZ information is only used for input and output of date/time, not for storing it. So I don't see where a mismatch would cause problems of that kind. >3) Should we move the timezone source code from src/timezone to >src/backend/timezone if only the backend is using the timezone >database. I'll defer that to you core people. >4) The timezone data files are installed in /pgsql/share/timezone. Is >that OK? I think so. But then I was the one to suggest it, so I'll defer to others to comment on this one too. >5) We only had a compiled-in location for /lib in the past for dynamic >loading, and had a GUC variable to override it. initdb always used >/share in a fixed location, but it has a flag to override it. With >/share/timezone, the server now requires the timezone database to be in >a fixed location too. Do we now need a sharedir GUC variable? I think Claudio is working on this. IIRC with the help of Tom and Peter. I'm not sure if share was specifically included in the list of directories he's working on, though, but several others. Claudio - correct me if I'm off target here. >6) Can someone get this code working on Unix. I get the right value >for timeofday(), but now() and CURRENT_TIMESTAMP return wrong and >different values. I can perhaps take a look at this a bit later, but if someone else can step up and do it sooner, I'd be happy to see that :-) >7) Why can't we just test for valid timezones by setting the timezone >string and checking that the timezone returned isn't GMT: > > $ TZ="abc" date > Sun May 2 11:02:26 GMT 2004 Good question. I *think* that should be safe. At least with the new TZ library. Not sure about others. >As you can see, our own timezone database has brought perhaps more >problems than it will solve. Not sure if I agree here. At least not in the win32 scope. //Magnus ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Timezone database questions 2004-05-02 11:55 Re: Timezone database questions Magnus Hagander <mha@sollentuna.net> @ 2004-05-02 12:07 ` Bruce Momjian <pgman@candle.pha.pa.us> 0 siblings, 0 replies; 5+ messages in thread From: Bruce Momjian @ 2004-05-02 12:07 UTC (permalink / raw) To: Magnus Hagander <mha@sollentuna.net>; +Cc: PostgreSQL-development <pgsql-hackers@postgresql.org>; pgsql-interfaces; PostgreSQL Win32 port list <pgsql-hackers-win32@postgresql.org> Magnus Hagander wrote: > >I have added a timezone database to CVS, and enabled it for > >Win32. This > >allows Win32 to pass our pre-1970 regression tests. There are also > >plans to enable this code under Unix so we have a standard database for > >all installs and so we can query for valid timezone names. > > > >However, this brings up some questions: > > > >1) How do we set the default local timezone for our database? The OS > >knows the local timezone. How do we set our local timezone on Win32? > >On Unix? (On Unix, there is usually an /etc/localtime file that is > >created during install.) Perhaps we can query the current timezone > >specification (e.g. EDT), and do some kind of lookup. I know of no way > >to get the full specification, e.g. EST5EDT or America/New_York. > > Could we just require that it is configured through postgresql.conf? > IIRC there is a parameter to set it there. IMO, that would be enough. We would get too many problem reports we would get if we always defaulted the database timezone to GMT. We have to set the default, perhaps during initdb. (I just posted something else explaining why we should have initdb set sharedir and libdir too.) > If not, we can use GetTimeZoneInformation(). It returns the standard > name (amongst other things) of the tz configured in the system. Yep, we will need that, and for Unix too. My guess is that we will need something in the postmaster startup that will set that default based on the OS timezone. > Or do nothing. On unix it defaults to the value of the TZ environment > variables. If you set that one before you start it, you get the same > behaviour. In that case, I'd still say we *recommend* setting it in > postgresql.conf, but it will still work. Right now I think it defaults to the OS timezone if you don't set PGTZ. We can't require everyone to set PGTZ to get a reasonable default timezone. > >2) Does ecpg need to use our database or can it use the local one? If > >it uses ours, it adds a requirement that all ecpg executables need > >access to our database in a compile-time-defined fixed directory. > >(Yuck.) If it does not, is it OK that there is a mismatch? I am sure > >we had this issue before because you could run clients and servers on > >different OS's, but when everything was on the same OS, there was no > >mismatch, while if ecpg uses the local OS, there will be a mismatch > >where there wasn't one before. > > As I've said before, I don't hink ecpg should use the same database. And > AFAIK the TZ information is only used for input and output of date/time, > not for storing it. So I don't see where a mismatch would cause problems > of that kind. OK, but Tom seemed to think it should be used for ecpg, but I am just asking. > >7) Why can't we just test for valid timezones by setting the timezone > >string and checking that the timezone returned isn't GMT: > > > > $ TZ="abc" date > > Sun May 2 11:02:26 GMT 2004 > > Good question. I *think* that should be safe. At least with the new TZ > library. Not sure about others. > > > >As you can see, our own timezone database has brought perhaps more > >problems than it will solve. > > Not sure if I agree here. At least not in the win32 scope. Yea, new code is a clearly good on Win32. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073 ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Timezone database questions @ 2004-05-02 18:07 Magnus Hagander <mha@sollentuna.net> 2004-05-02 18:16 ` Re: Timezone database questions Bruce Momjian <pgman@candle.pha.pa.us> 0 siblings, 1 reply; 5+ messages in thread From: Magnus Hagander @ 2004-05-02 18:07 UTC (permalink / raw) To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: PostgreSQL-development <pgsql-hackers@postgresql.org>; pgsql-interfaces; PostgreSQL Win32 port list <pgsql-hackers-win32@postgresql.org> >> >1) How do we set the default local timezone for our >database? The OS >> >knows the local timezone. How do we set our local timezone >on Win32? >> >On Unix? (On Unix, there is usually an /etc/localtime file that is >> >created during install.) Perhaps we can query the current timezone >> >specification (e.g. EDT), and do some kind of lookup. I >know of no way >> >to get the full specification, e.g. EST5EDT or America/New_York. >> >> Could we just require that it is configured through postgresql.conf? >> IIRC there is a parameter to set it there. IMO, that would be enough. > >We would get too many problem reports we would get if we always >defaulted the database timezone to GMT. We have to set the default, >perhaps during initdb. (I just posted something else explaining why we >should have initdb set sharedir and libdir too.) Set the defualt timezone at initdb sounds a bit weird. Again, timezone stuff is only used to present data, not to change it. You can even change it during a transaction. Setting the default at inidb seems weird. If you want to set the default, use postgresql.conf. Otherwise, we need to pick it up somewhere else. >> If not, we can use GetTimeZoneInformation(). It returns the standard >> name (amongst other things) of the tz configured in the system. > >Yep, we will need that, and for Unix too. Can't tell you how to do it on Unix :-( >> Or do nothing. On unix it defaults to the value of the TZ environment >> variables. If you set that one before you start it, you get the same >> behaviour. In that case, I'd still say we *recommend* setting it in >> postgresql.conf, but it will still work. > >Right now I think it defaults to the OS timezone if you don't >set PGTZ. >We can't require everyone to set PGTZ to get a reasonable default >timezone. Note - TZ, not PGTZ. At least it says so in the postgresql.conf that is installed. So it's at least a standard env variable, and not a PG only one. I don't know how often this one is actually set, though. //Magnus ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Timezone database questions 2004-05-02 18:07 Re: Timezone database questions Magnus Hagander <mha@sollentuna.net> @ 2004-05-02 18:16 ` Bruce Momjian <pgman@candle.pha.pa.us> 0 siblings, 0 replies; 5+ messages in thread From: Bruce Momjian @ 2004-05-02 18:16 UTC (permalink / raw) To: Magnus Hagander <mha@sollentuna.net>; +Cc: PostgreSQL-development <pgsql-hackers@postgresql.org>; pgsql-interfaces; PostgreSQL Win32 port list <pgsql-hackers-win32@postgresql.org> Magnus Hagander wrote: > >> >1) How do we set the default local timezone for our > >database? The OS > >> >knows the local timezone. How do we set our local timezone > >on Win32? > >> >On Unix? (On Unix, there is usually an /etc/localtime file that is > >> >created during install.) Perhaps we can query the current timezone > >> >specification (e.g. EDT), and do some kind of lookup. I > >know of no way > >> >to get the full specification, e.g. EST5EDT or America/New_York. > >> > >> Could we just require that it is configured through postgresql.conf? > >> IIRC there is a parameter to set it there. IMO, that would be enough. > > > >We would get too many problem reports we would get if we always > >defaulted the database timezone to GMT. We have to set the default, > >perhaps during initdb. (I just posted something else explaining why we > >should have initdb set sharedir and libdir too.) > > Set the defualt timezone at initdb sounds a bit weird. Again, timezone > stuff is only used to present data, not to change it. You can even > change it during a transaction. Setting the default at inidb seems > weird. If you want to set the default, use postgresql.conf. Otherwise, > we need to pick it up somewhere else. Yea, sorry, I meant to say we need to pick up the local timezone on postmaster start. > >> If not, we can use GetTimeZoneInformation(). It returns the standard > >> name (amongst other things) of the tz configured in the system. > > > >Yep, we will need that, and for Unix too. > > Can't tell you how to do it on Unix :-( > > > >> Or do nothing. On unix it defaults to the value of the TZ environment > >> variables. If you set that one before you start it, you get the same > >> behaviour. In that case, I'd still say we *recommend* setting it in > >> postgresql.conf, but it will still work. > > > >Right now I think it defaults to the OS timezone if you don't > >set PGTZ. > >We can't require everyone to set PGTZ to get a reasonable default > >timezone. > > Note - TZ, not PGTZ. At least it says so in the postgresql.conf that is > installed. So it's at least a standard env variable, and not a PG only > one. I don't know how often this one is actually set, though. Yes, sorry, TZ. It is not set on my box. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073 ^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2004-05-02 18:16 UTC | newest] Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2004-05-02 11:11 Timezone database questions Bruce Momjian <pgman@candle.pha.pa.us> 2004-05-02 11:55 Re: Timezone database questions Magnus Hagander <mha@sollentuna.net> 2004-05-02 12:07 ` Bruce Momjian <pgman@candle.pha.pa.us> 2004-05-02 18:07 Re: Timezone database questions Magnus Hagander <mha@sollentuna.net> 2004-05-02 18:16 ` Bruce Momjian <pgman@candle.pha.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox