Timezone database questions - Mailing list pgsql-hackers

From Bruce Momjian
Subject Timezone database questions
Date
Msg-id 200405021111.i42BBnM19329@candle.pha.pa.us
Whole thread Raw
List pgsql-hackers
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

pgsql-hackers by date:

Previous
From: Fabien COELHO
Date:
Subject: Re: pg ANY/SOME ambiguity wrt sql standard?
Next
From: Bruce Momjian
Date:
Subject: Fixed directory locations in installs