>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

Sun, 2 May 2004 13:55:42 +0200
"Magnus Hagander" <mha@sollentuna.net>
Re: Timezone database questions
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

Sun, 2 May 2004 08:07:53 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Timezone database questions