Re: Timezone database questions

>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