Re: [pgsql-hackers-win32] Timezone database questions - Mailing list pgsql-hackers

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