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

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

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

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

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.

/share was in the list. I've received no reply from Tom or Peter.

Only solution so far is the one rejected a little over a month ago. Unless
someone pipes up soon, I'm just going to re-ask that it be accepted (iirc,
Bruce had an off-list idea he was going to post too).

Cheers,
Claudio

--- 
Certain disclaimers and policies apply to all email sent from Memetrics.
For the full text of these disclaimers and policies see 
<a
href="http://www.memetrics.com/emailpolicy.html">http://www.memetrics.com/em
ailpolicy.html</a>