Обсуждение: timezones to own config file

I'd like to know what exactly is to be done for this TODO-item
   o Allow customization of the known set of TZ names (generalize the     present australian_timezones hack)

The most recent mail in the archives about this subject seems to be:

http://archives.postgresql.org/pgsql-general/2006-04/msg00966.php

So we'd need a file specifying time zone abbreviations and their offsets, so
for example a file containing lines like:

XST     -3.25     # comment here for Xtended Standard Time

The default file would contain the now hardwired values from datetime.c.
Postmaster would parse the file at least on startup or during SIGHUP as well
and (after a few checks) merge the list with the remaining values from
datetime.c (keeping them ordered alphabetically).

I wondered especially if there was a need to associate the abbreviation in
some way to the entries from the zic database but datetime.c seems to just
add or subtract the offset and save the resulting time without further time
zone information.


Thanks,
Joachim

Joachim Wieland <joe@mcknight.de> writes:
> I'd like to know what exactly is to be done for this TODO-item
>     o Allow customization of the known set of TZ names (generalize the
>       present australian_timezones hack)

Well, part of the TODO is to figure out exactly what to do ;-)

> So we'd need a file specifying time zone abbreviations and their offsets, so
> for example a file containing lines like:
> XST     -3.25     # comment here for Xtended Standard Time

That's the basic idea.  It strikes me that there are going to be certain
groups of settings that go together, ie, folks in North America are
going to know what they want, the Aussies will know what they want but
it'll be different, etc.  I was toying with the idea that what we should
have is a postgresql.conf setting that points at a particular file
containing TZ names.  Then you could imagine that the standard distro
installs
{prefix}/share/tznames/americas{prefix}/share/tznames/australia{prefix}/share/tznames/fareast

and in postgresql.conf you put
tznames = australia

or if you live somewhere well and truly off the beaten track, you make
your own tznames file, put it in that directory, and specify it in
postgresql.conf.  But 99% of users should not have to do that --- they
should be able to select an already-configured tzname set and have it
Do What They Want.

But that's just one idea.  Feel free to propose something else.

> I wondered especially if there was a need to associate the abbreviation in
> some way to the entries from the zic database but datetime.c seems to just
> add or subtract the offset and save the resulting time without further time
> zone information.

The zic database doesn't seem to have a problem with using the same
abbreviations to mean many different things.  We could look to it for
information, or maybe even use its classification of timezone groups,
but I don't think it can solve the problem for us.

I think you may be thinking of yet a separate TODO item, which is to
be able to use the zic timezone names in timestamptz input, viz'2006-05-24 21:11 Americas/New_York'::timestamptz
But names like 'IST' or 'CDT' are not zic timezone names, they just
represent specific offsets from UTC.
        regards, tom lane

On Wed, May 24, 2006 at 09:13:42PM -0400, Tom Lane wrote:
> The zic database doesn't seem to have a problem with using the same
> abbreviations to mean many different things.  We could look to it for
> information, or maybe even use its classification of timezone groups,
> but I don't think it can solve the problem for us.
>
> I think you may be thinking of yet a separate TODO item, which is to
> be able to use the zic timezone names in timestamptz input, viz
>     '2006-05-24 21:11 Americas/New_York'::timestamptz
> But names like 'IST' or 'CDT' are not zic timezone names, they just
> represent specific offsets from UTC.

Well, the zic database does contain information about the
abbreviations, so we would be able to build a list of them. I think the
right solution is probably fix the above first (allow full zic
timezones in timestamps) and then setup the CST/CEST/etc as a list of
aliases users can customise...

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Martijn,

On Fri, May 26, 2006 at 03:03:15PM +0200, Martijn van Oosterhout wrote:
> > I think you may be thinking of yet a separate TODO item, which is to
> > be able to use the zic timezone names in timestamptz input, viz
> >     '2006-05-24 21:11 Americas/New_York'::timestamptz
> > But names like 'IST' or 'CDT' are not zic timezone names, they just
> > represent specific offsets from UTC.

> Well, the zic database does contain information about the
> abbreviations, so we would be able to build a list of them.

That's what i've done already  :-)

> I think the right solution is probably fix the above first (allow full zic
> timezones in timestamps) and then setup the CST/CEST/etc as a list of
> aliases users can customise...

Why do you think that full zic timezone in timestamps should be done first?
For me, both features are independent, but maybe I've missed something.

As I understand it, the time zone abbreviations are not aliases for full zic
names but only for offsets.

So if you set "Region/City" as the timezone, the offset depends on the year
(because countries have changed their timezones in the past) and whether or
not DST is or was active at that time.

On the other hand, a timezone abbreviation only means "GMT + x hours" and
nothing more. The relation between both now is that a "Region/City" timezone
changes its timezone abbreviation over the years (to reflect changes to
timezones done in the past) and during the year (to reflect changes due to
daylight saving time). And this is actually what the zic database is all
about.


Joachim