While looking at this recent bug report (which still fails in CVS tip)
http://archives.postgresql.org/pgsql-bugs/2003-02/msg00094.php
I realized that the code paths that putatively exist for machines
with neither HAVE_TM_ZONE nor HAVE_INT_TIMEZONE have gone unused
since at least 6.5.  Proof is that abstime2tm() doesn't even compile
in that code path (it has a reference to an undefined variable "now").

Since we evidently have no supported platforms that have neither method
of learning the timezone, I propose that we stop contorting the code
with the illusion that we can handle this case reasonably.  We can
easily set it up so that we just default to GMT when neither config
symbol is defined.

The reason I want to do this is to remove the dependency on
system-supplied values of CTimeZone and CTZName.  CTZName can go
away altogether (ditto CDayLight), and CTimeZone will only be used
when the user explicitly sets a timezone as a numeric offset from
GMT (ie, the HasCTZSet paths).  This will save cycles during every
transaction start, where we currently expend time setting these values
(see GetCurrentAbsoluteTimeUsec).  And it will fix the above-mentioned
bug, which exists because CTimeZone is set at transaction start and not
updated by a later SET TIMEZONE command.

The bug could be fixed, sort of, by calling GetCurrentAbsoluteTimeUsec
again after executing SET TIMEZONE.  But there is a variant scenario
where the same bug exists: if there has been a daylight-savings
transition since the current transaction started, we'll still get the
wrong answers.  So trying to make CTimeZone track the current timezone
correctly seems doomed to failure anyhow.

Any objections?

			regards, tom lane

Wed, 19 Feb 2003 22:35:58 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Simplifying timezone support
On Wed, Feb 19, 2003 at 10:35:58PM -0500, Tom Lane wrote:
<snip Tom discussion backend internal tracking of timezone> 
> Any objections?

Not to your suggestion per se, but looking at the bug report raises a
question about pgsql's time zone parsers. It appears there's at least
two, since SET TIME ZONE accepts strings like 'US/Eastern', while general
timestamp parsing doesn't:

test=# select TIMESTAMP WITH TIME ZONE '2003/02/18 09:36:06.00933 CST';
         timestamptz          
------------------------------
 2003-02-18 09:36:06.00933-06
(1 row)

test=# select TIMESTAMP WITH TIME ZONE '2003/02/18 09:36:06.00933 EST';
         timestamptz          
------------------------------
 2003-02-18 08:36:06.00933-06
(1 row)

test=# select TIMESTAMP WITH TIME ZONE '2003/02/18 09:36:06.00933 US/Eastern';
ERROR:  Bad timestamp external representation '2003/02/18 09:36:06.00933 US/Eastern'

Further testing says it's even worse that that: 

SET TIME ZONE will silently accept any string at all, and fall back to
providing GMT when a timestamptz is requested. This includes the TLA
TZ abbreviations that the constant parsing code understands, like CST
and EST.

test=# set TIME ZONE 'CST';
SET
test=# select TIMESTAMP WITH TIME ZONE '2003/02/18 09:36:06.00933 EST';
         timestamptz          
------------------------------
 2003-02-18 14:36:06.00933+00
(1 row)

test=# set TIME ZONE 'FOOBAR';
SET
test=# select TIMESTAMP WITH TIME ZONE '2003/02/18 09:36:06.00933 EST';
         timestamptz          
------------------------------
 2003-02-18 14:36:06.00933+00
(1 row)

Here's an especially fun one: with DATESTYLE set to 'Postgresql,US', whatever
string is handed to SET TIME ZONE comes out the other end, if it can't
be parsed:

test=# set TIME ZONE 'FOOBAR';
SET
test=# select TIMESTAMP WITH TIME ZONE '2003/02/18 09:36:06.00933 EST';
              timestamptz              
---------------------------------------
 Tue Feb 18 14:36:06.00933 2003 FOOBAR
(1 row)


Leading to this erroneous pair:

test=# set TIME ZONE 'US/Central';
SET
test=# select TIMESTAMP WITH TIME ZONE '2003/02/18 09:36:06.00933 EST';
            timestamptz             
------------------------------------
 Tue Feb 18 08:36:06.00933 2003 CST
(1 row)

test=# set TIME ZONE 'CST';
SET
test=# select TIMESTAMP WITH TIME ZONE '2003/02/18 09:36:06.00933 EST';
            timestamptz             
------------------------------------
 Tue Feb 18 14:36:06.00933 2003 CST
(1 row)

test=# 

Tom, since you're in (or near) that code right now, how painful would
it be to unify the time zone parsing? What's the correct behavior?
Certainly SET TIME ZONE should at leat NOTICE about invalide time zone
names?

Ross

Thu, 20 Feb 2003 13:34:53 -0600
"Ross J. Reedstrom" <reedstrm@rice.edu>
Re: Simplifying timezone support
"Ross J. Reedstrom" <reedstrm@rice.edu> writes:
> question about pgsql's time zone parsers. It appears there's at least
> two, since SET TIME ZONE accepts strings like 'US/Eastern', while general
> timestamp parsing doesn't:

The TIME ZONE string is fed to libc (via TZ environment variable); the
other cases are not.

> SET TIME ZONE will silently accept any string at all, and fall back to
> providing GMT when a timestamptz is requested.

Provide a portable way of getting libc to tell us whether it likes TZ,
and I'll be glad to fix this.

Ultimately we should probably get rid of our dependence on the libc
time routines altogether ... but I have no intention of opening that
can of worms right now.  See past discussions in the archives.

			regards, tom lane

Thu, 20 Feb 2003 15:21:09 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Simplifying timezone support 
On Thu, Feb 20, 2003 at 03:21:09PM -0500, Tom Lane wrote:
> "Ross J. Reedstrom" <reedstrm@rice.edu> writes:
> > question about pgsql's time zone parsers. It appears there's at least
> > two, since SET TIME ZONE accepts strings like 'US/Eastern', while general
> > timestamp parsing doesn't:
> 
> The TIME ZONE string is fed to libc (via TZ environment variable); the
> other cases are not.
> 
> > SET TIME ZONE will silently accept any string at all, and fall back to
> > providing GMT when a timestamptz is requested.
> 
> Provide a portable way of getting libc to tell us whether it likes TZ,
> and I'll be glad to fix this.

Dang that lovely word 'portable'. However, given your proposed change,
perhaps the hurdle for portable time handling is now lower: it seems we've
not been exposed to as broad a range of broken systems as in the past.
I'll look at it. but no promises.

> Ultimately we should probably get rid of our dependence on the libc
> time routines altogether ... but I have no intention of opening that
> can of worms right now.  See past discussions in the archives.

Agreed. I see we're inheriting the actually misleading case from the
OS/libc, as well:

wallace$ unset TZ
wallace$ date
Thu Feb 20 15:00:04 CST 2003
wallace$ export TZ=US/Central
wallace$ date
Thu Feb 20 15:00:16 CST 2003
wallace$ export TZ=US/Zanzibar
wallace$ date
Thu Feb 20 21:00:33 US/Zanzibar 2003
wallace$ export TZ=CST
wallace$ date
Thu Feb 20 21:00:42 CST 2003
wallace$ 

Ross

Thu, 20 Feb 2003 15:03:07 -0600
"Ross J. Reedstrom" <reedstrm@rice.edu>
Re: Simplifying timezone support
"Ross J. Reedstrom" <reedstrm@rice.edu> writes:
> On Thu, Feb 20, 2003 at 03:21:09PM -0500, Tom Lane wrote:
>> Provide a portable way of getting libc to tell us whether it likes TZ,
>> and I'll be glad to fix this.

> Dang that lovely word 'portable'. However, given your proposed change,
> perhaps the hurdle for portable time handling is now lower: it seems we've
> not been exposed to as broad a range of broken systems as in the past.

On this particular point my threshold of 'portable' is actually pretty
low, as long as it's fail-soft.  Failure to detect bad TZ on some
systems would leave them no worse off than before, right?

But I haven't seen *any* published API that directly tells you whether
tzset liked TZ or not --- AFAICT it's supposed to just silently
substitute GMT.  Which would be okay if "GMT" were the only allowed
spelling of GMT, but it ain't ...

			regards, tom lane

Thu, 20 Feb 2003 16:19:21 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Simplifying timezone support 
On Thu, Feb 20, 2003 at 04:19:21PM -0500, Tom Lane wrote:
> "Ross J. Reedstrom" <reedstrm@rice.edu> writes:
> > On Thu, Feb 20, 2003 at 03:21:09PM -0500, Tom Lane wrote:
> >> Provide a portable way of getting libc to tell us whether it likes TZ,
> >> and I'll be glad to fix this.
> 
> > Dang that lovely word 'portable'. However, given your proposed change,
> > perhaps the hurdle for portable time handling is now lower: it seems we've
> > not been exposed to as broad a range of broken systems as in the past.
> 
> On this particular point my threshold of 'portable' is actually pretty
> low, as long as it's fail-soft.  Failure to detect bad TZ on some
> systems would leave them no worse off than before, right?
> 
> But I haven't seen *any* published API that directly tells you whether
> tzset liked TZ or not --- AFAICT it's supposed to just silently
> substitute GMT.  Which would be okay if "GMT" were the only allowed
> spelling of GMT, but it ain't ...

I've been digging in the date and time code a bit, and now have a proposal
for dealing with SET TIME ZONE 'someunknownstring'.  First, we use the
time token table from the time constant parser in utils/adt/datetime.c
to see if we've got a recognized time zone abbreviation. If it is,
we generate a canonical POSIX timezone name for use in setting TZ,
call tzset(), and we're done.

If the time zone came back UNKOWN, we go ahead and see if tzset() can
interpret it. Criteria for failure: if the timezone offset came back 0,
and the reported tzname[0] is the same as the string that we passed in. If
it does, we fire a NOTICE about an unknown spelling of GMT. Note that we
would have already caught all _known_ spellings of GMT in the first step,
so we won't be spamming the DBA with warnings about 'GMT' and 'UTC', etc.

An extension to this would be to use the tzset() trick above directly
in the datetime constant parser, as a fallback after not matching the
table. In that case, we'd probably want to treat the unknown spelling
of GMT as an error, though (as it currently does).

Thoughts? If this seems acceptable, I can implement it this weekend.

Ross

Fri, 21 Feb 2003 16:14:57 -0600
"Ross J. Reedstrom" <reedstrm@rice.edu>
Re: Simplifying timezone support
"Ross J. Reedstrom" <reedstrm@rice.edu> writes:
> If the time zone came back UNKOWN, we go ahead and see if tzset() can
> interpret it. Criteria for failure: if the timezone offset came back 0,
> and the reported tzname[0] is the same as the string that we passed in. If
> it does, we fire a NOTICE about an unknown spelling of GMT. Note that we
> would have already caught all _known_ spellings of GMT in the first step,
> so we won't be spamming the DBA with warnings about 'GMT' and 'UTC', etc.

I'm worried about cases like "Africa/Benin" for places that just happen
to be on the prime meridian, but don't call their time GMT or UTC.
Looking at a globe, it also seems possible that there are places an hour
west of Greenwich, for which this could fail during daylight-savings
season.

> An extension to this would be to use the tzset() trick above directly
> in the datetime constant parser, as a fallback after not matching the
> table. In that case, we'd probably want to treat the unknown spelling
> of GMT as an error, though (as it currently does).

I think tzset() is probably much too slow to consider calling on every
pass through timestamptz_in ...

			regards, tom lane

Fri, 21 Feb 2003 18:15:31 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Simplifying timezone support 
On Fri, Feb 21, 2003 at 06:15:31PM -0500, Tom Lane wrote:
> "Ross J. Reedstrom" <reedstrm@rice.edu> writes:
<snip>
> 
> I'm worried about cases like "Africa/Benin" for places that just happen
> to be on the prime meridian, but don't call their time GMT or UTC.
> Looking at a globe, it also seems possible that there are places an hour
> west of Greenwich, for which this could fail during daylight-savings
> season.

Well, that'll either get caught by the existing table (we've got six
different spellings of GMT, currently) or by the 'string in != string out'
case - the zoneinfo format requires a 3 or more character abbreviation
for the time zone. For every case I'v looked at in my zoneinfo directory,
it's either 3 or 4 uppercase characters, and _never_ matches the filename
path string used to set it. I'll do an exhaustive test after dinner.

> 
> > An extension to this would be to use the tzset() trick above directly
> > in the datetime constant parser, as a fallback after not matching the
> > table. In that case, we'd probably want to treat the unknown spelling
> > of GMT as an error, though (as it currently does).
> 
> I think tzset() is probably much too slow to consider calling on every
> pass through timestamptz_in ...

It wouldn't happen on every call - only with funky timezone
representations.  We could NOTICE use of tzset(), as well, to alert the
DBA about something fishy, if you'd like.

Ross

Fri, 21 Feb 2003 17:45:54 -0600
"Ross J. Reedstrom" <reedstrm@rice.edu>
Re: Simplifying timezone support
On Fri, Feb 21, 2003 at 05:45:53PM -0600, Ross J. Reedstrom wrote:
> On Fri, Feb 21, 2003 at 06:15:31PM -0500, Tom Lane wrote:
> > "Ross J. Reedstrom" <reedstrm@rice.edu> writes:
> <snip>
> > 
> > I'm worried about cases like "Africa/Benin" for places that just happen
> > to be on the prime meridian, but don't call their time GMT or UTC.
> > Looking at a globe, it also seems possible that there are places an hour
> > west of Greenwich, for which this could fail during daylight-savings
> > season.
> 
> Well, that'll either get caught by the existing table (we've got six
> different spellings of GMT, currently) or by the 'string in != string out'
> case - the zoneinfo format requires a 3 or more character abbreviation
> for the time zone. For every case I'v looked at in my zoneinfo directory,
> it's either 3 or 4 uppercase characters, and _never_ matches the filename
> path string used to set it. I'll do an exhaustive test after dinner.

O.K., I've run the test: of the 1108 files in my zoneinfo database,
only 11 have matching filenames to the canonical name returned after
setting TZ.  Of those 11, 4 are some version of GMT (GMT, UCT, UTC,
WET), of which, one is in fact missing from our table - UCT. At minimum,
I'll add that.

Every other validly formatted TZ variable that returns GMT should be
caught be the datetktbl check.

I'll play with it this weekend, see how hard it is to make it work.

Ross

Fri, 21 Feb 2003 20:39:12 -0600
"Ross J. Reedstrom" <reedstrm@rice.edu>
Re: Simplifying timezone support
On Fri, Feb 21, 2003 at 08:39:12PM -0600, Ross J. Reedstrom wrote:
> 
> Every other validly formatted TZ variable that returns GMT should be
> caught be the datetktbl check.
> 
> I'll play with it this weekend, see how hard it is to make it work.

O.K., the weekend's over, And I've created two different version
of this.  Both work, ipass all the regression test, and solve the
'CST is just a funny way to say GMT' problem.  I was able to make use
of DecodePosixTimezone (DPT) from Thomas's datetime parsing code in
assign_timezone. However, the order of application of this vis. tzset
is unclear.

I had proposed doing the DPT first, then tzset, then a NOTICE if it
looked like tzset didn't. Got that working, but discovered a change of
behavior: for some of those who have a timezone in the zoneinfo database
that is a three letter abbreviation, the current code (tzset only) will
provide daylight savings time transitions, so that a timestamp in July
returns a different timezone than one in February.  This is not true for
our internal values of set time zone: there, we convert to a numerical
offset, which is constant no matter when the timestamp occurs.

This is still a win for those who's timezone abbreviation is _not_ in the
zoneinfo DB, (such as CST), which currently is silently interpreted as
an odd spelling of GMT.

Second solution - try tzset() first, and apply the following heuristic
to see if it took:

tzname[0]==$TZ and tzname[1]=="" and timezone=0 and daylight=0

In other words, _all_ the timezone related information remains the
default.  I tested this against the 1607 zoneinfo files on my system:
every one was filtered out, even things that _are_ GMT with no DST (they
all had a non-null tzname[1] == tzname[0])

If this succeeds (i.e. tzset didn't recognize the TZ), go ahead and look
it up in our big table'o date/time strings. This also works, fixing the
bogus GMT spellings, without changing current behavior for any string
that is not bogus.

Note that the sysadmin can always tell if tzset or the table was used, by
looking at the format of the 'show time zone' result. If tzset was called,
this is the string that was passed to 'set time zone'. If the table was
used, it will be an hours west of GMT offset.

The problem with this approach is that it does nothing to reduce our 
dependency on the OS timezone functionality.

Comments? I've attached the second patch for discussion.

Ross
Mon, 24 Feb 2003 15:34:57 -0600
"Ross J. Reedstrom" <reedstrm@rice.edu>
Re: Simplifying timezone support
According to my sent folder, this went out Monday afternoon, but I
haven't seen it yet, so I'm resending to the list only, without the
attached patch.  I'll send the patch over to patches.

Any comment on the behavior, specifically, the heuristic for deciding
tzset() failed, and the proposed order of application of tzset()
vs. table lookup?

Ross

On Mon, Feb 24, 2003 at 03:34:56PM -0600, Ross J. Reedstrom wrote:
> On Fri, Feb 21, 2003 at 08:39:12PM -0600, Ross J. Reedstrom wrote:
> > 
> > Every other validly formatted TZ variable that returns GMT should be
> > caught be the datetktbl check.
> > 
> > I'll play with it this weekend, see how hard it is to make it work.
> 
> O.K., the weekend's over, And I've created two different version
> of this.  Both work, ipass all the regression test, and solve the
> 'CST is just a funny way to say GMT' problem.  I was able to make use
> of DecodePosixTimezone (DPT) from Thomas's datetime parsing code in
> assign_timezone. However, the order of application of this vis. tzset
> is unclear.
> 
> I had proposed doing the DPT first, then tzset, then a NOTICE if it
> looked like tzset didn't. Got that working, but discovered a change of
> behavior: for some of those who have a timezone in the zoneinfo database
> that is a three letter abbreviation, the current code (tzset only) will
> provide daylight savings time transitions, so that a timestamp in July
> returns a different timezone than one in February.  This is not true for
> our internal values of set time zone: there, we convert to a numerical
> offset, which is constant no matter when the timestamp occurs.
> 
> This is still a win for those who's timezone abbreviation is _not_ in the
> zoneinfo DB, (such as CST), which currently is silently interpreted as
> an odd spelling of GMT.
> 
> Second solution - try tzset() first, and apply the following heuristic
> to see if it took:
> 
> tzname[0]==$TZ and tzname[1]=="" and timezone=0 and daylight=0
> 
> In other words, _all_ the timezone related information remains the
> default.  I tested this against the 1607 zoneinfo files on my system:
> every one was filtered out, even things that _are_ GMT with no DST (they
> all had a non-null tzname[1] == tzname[0])
> 
> If this succeeds (i.e. tzset didn't recognize the TZ), go ahead and look
> it up in our big table'o date/time strings. This also works, fixing the
> bogus GMT spellings, without changing current behavior for any string
> that is not bogus.
> 
> Note that the sysadmin can always tell if tzset or the table was used, by
> looking at the format of the 'show time zone' result. If tzset was called,
> this is the string that was passed to 'set time zone'. If the table was
> used, it will be an hours west of GMT offset.
> 
> The problem with this approach is that it does nothing to reduce our 
> dependency on the OS timezone functionality.
> 
> Comments? I've attached the second patch for discussion.
> 
> Ross
> 

Thu, 27 Feb 2003 10:13:29 -0600
"Ross J. Reedstrom" <reedstrm@rice.edu>
Re: Simplifying timezone support
Here's the patch I stripped out of my hackers post from Monday (that still
hasn't shown up). 

This is for discussion of approach, not for immediate application,
alothugh it does pass all the regresion tests.

Ross
Thu, 27 Feb 2003 10:31:01 -0600
"Ross J. Reedstrom" <reedstrm@rice.edu>
Re: [HACKERS] Simplifying timezone support

Ross, can you elaborate on what this attempts to fix, and what still
needs to be done.

---------------------------------------------------------------------------

Ross J. Reedstrom wrote:
> Here's the patch I stripped out of my hackers post from Monday (that still
> hasn't shown up). 
> 
> This is for discussion of approach, not for immediate application,
> alothugh it does pass all the regresion tests.
> 
> Ross
> 

[ Attachment, skipping... ]

> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly

-- 
  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

Mon, 17 Mar 2003 17:06:02 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [HACKERS] Simplifying timezone support

Where are we on this?

---------------------------------------------------------------------------

Ross J. Reedstrom wrote:
> Here's the patch I stripped out of my hackers post from Monday (that still
> hasn't shown up). 
> 
> This is for discussion of approach, not for immediate application,
> alothugh it does pass all the regresion tests.
> 
> Ross
> 

[ Attachment, skipping... ]

> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly

-- 
  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

Wed, 19 Mar 2003 16:06:59 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [HACKERS] Simplifying timezone support
Awhile back, "Ross J. Reedstrom" <reedstrm@rice.edu> wrote:
> Second solution - try tzset() first, and apply the following heuristic
> to see if it took:
> tzname[0]==$TZ and tzname[1]=="" and timezone=0 and daylight=0

I finally went back to look at this issue, and soon realized that the
above test is in fact glibc-specific.  Other implementations of tzset()
may act differently.  On HPUX, tzset() appears to adopt the system-wide
setting (from /etc/zoneinfo/localtime) if TZ contains a garbage string.
And yet, that's a better fallback behavior than adopting GMT.

I have applied a patch that just checks for tzname[1] not empty or
timezone != 0; either one means that tzset was able to interpret TZ as a
non-GMT zone (or that it gave up and reverted to a non-GMT default zone,
which we can't distinguish anyway).  Failing that, it looks to see if
the string is known as a GMT equivalent to DecodeTimezone.  If not, it
complains.

I did not include Ross' idea of accepting other zone names known to
DecodeTimezone as if they were a numeric-offset value.  We could still
do that if people like it, but it seemed a bigger change in behavior
than just trying to check that tzset() worked.

I also added code that detects and rejects a leap-second-aware timezone
definition, since we've now seen a couple different reports of systems
where such zones seem to be installed by default, leading to breakage
of our date/time arithmetic.

			regards, tom lane

Sat, 17 May 2003 21:20:44 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Simplifying timezone support 