Обсуждение: AW: Re: tinterval - operator problems on AIX

> The time zone is now evaluated in the time zone of the result, rather
> than the input, using system support routines from libc. 

Ok, I have the answer.

On AIX mktime(3) leaves tm_isdst at -1 if it does not have timezone 
info for that particular year and returns -1.
From man page:The mktime subroutine returns the specified time in seconds encoded as a value oftype time_t. If the time
cannotbe represented, the function returns the value(time_t)-1.
 

The following code then makes savings time out of the -1.                                       tz = (tm->tm_isdst ?
(timezone- 3600) : timezone);
 

What now ?

Andreas

> On AIX mktime(3) leaves tm_isdst at -1 if it does not have timezone
> info for that particular year and returns -1.
> The following code then makes savings time out of the -1.
>   tz = (tm->tm_isdst ? (timezone - 3600) : timezone);

Hmm. That description is consistant with what I see in the Linux man
page. So I should check for (tm->tm_isdst > 0) rather than checking for
non-zero?

Would you like to test that on your machine? I'll try it here, and if
successful will consider this a bug report and a necessary fix for 7.1.

It is interesting that this is the only place in all of our code which
tickles this bug; afaik this line of code appears in other places too.
Can you find a case where the current code fails when you are not doing
arithmetic? Perhaps there are some more tests we could include in the
regression tests??

It is also interesting that afaik AIX is the only machine exhibiting
this problem. The before-1970 range of dates is known to occur in some
use cases, and having *something* in the timezone database isn't that
difficult :/
                      - Thomas

Thomas Lockhart writes:> > On AIX mktime(3) leaves tm_isdst at -1 if it does not have timezone> > info for that
particularyear and returns -1.> > The following code then makes savings time out of the -1.> >   tz = (tm->tm_isdst ?
(timezone- 3600) : timezone);> > Hmm. That description is consistant with what I see in the Linux> man page. So I
shouldcheck for (tm->tm_isdst > 0) rather than> checking for non-zero?> > Would you like to test that on your machine?
I'lltry it here, and> if successful will consider this a bug report and a necessary fix> for 7.1.
 

I have machines running AIX 4.1.5, 4.2.1, and 4.3.3 if you would like
to send me your test programs.
-- 
Pete Forman                 -./\.- Disclaimer: This post is originated
WesternGeco                   -./\.-  by myself and does not represent
pete.forman@westerngeco.com     -./\.-  opinion of Schlumberger, Baker
http://www.crosswinds.net/~petef  -./\.-  Hughes or their divisions.

> I have machines running AIX 4.1.5, 4.2.1, and 4.3.3 if you would like
> to send me your test programs.

I haven't yet actually fixed the code, but will post patches when I've
done so (assuming that a fix is possible).
                   - Thomas

Thomas Lockhart writes:
 > I haven't yet actually fixed the code, but will post patches when
 > I've done so (assuming that a fix is possible).

The normalization in this example program which subtracts 34 years
seems to work OK.  I've run it on AIX, IRIX, Linux and Solaris.  Some
examples follow.

AIX:
$ TZ=PST8PDT ago34 851995921
Local Mon Dec 30 17:32:01 1996 PST PST
      1996-12-30T17:32:01, wday=1, yday=364, isdst = 0
  UTC Tue Dec 31 01:32:01 1996 PST PST
      1996-12-31T01:32:01, wday=2, yday=365, isdst = 0
Local Sun Dec 30 17:32:01 1962 PST PST
      1962-12-30T17:32:01, wday=0, yday=363, isdst = 0
  UTC Mon Dec 31 01:32:01 1962 PST PST
      1962-12-31T01:32:01, wday=1, yday=364, isdst = 0

Linux:
$ TZ=America/Los_Angeles ago34 426475921
Local Thu Jul 07 18:32:01 1983 PDT -0700
      1983-07-07T18:32:01, wday=4, yday=187, isdst = 1
  UTC Fri Jul 08 01:32:01 1983 GMT +0000
      1983-07-08T01:32:01, wday=5, yday=188, isdst = 0
Local Thu Jul 07 18:32:01 1949 PST -0800
      1949-07-07T18:32:01, wday=4, yday=187, isdst = 0
  UTC Fri Jul 08 02:32:01 1949 GMT +0000
      1949-07-08T02:32:01, wday=5, yday=188, isdst = 0

Here is the program.  The call to localtime(&t_ago) is redundant and
hence the adjustment of t_ago can be skipped.  It is in this program
as a sanity check.

As it stands, this program assumes that the input and resulting date
are in the usual UNIX range of [1901, 2038].  I presume that there is
code in place that checks the range of dates.



--
Pete Forman                 -./\.- Disclaimer: This post is originated
WesternGeco                   -./\.-  by myself and does not represent
pete.forman@westerngeco.com     -./\.-  opinion of Schlumberger, Baker
http://www.crosswinds.net/~petef  -./\.-  Hughes or their divisions.

> Here is the program.  The call to localtime(&t_ago) is redundant and
> hence the adjustment of t_ago can be skipped.  It is in this program
> as a sanity check.
> As it stands, this program assumes that the input and resulting date
> are in the usual UNIX range of [1901, 2038].  I presume that there is
> code in place that checks the range of dates.

Interesting idea. I'm not sure that assuming that timezones from 1943
are the same as timezones from 2013 (they are not, at least in the US)
is any more valid than just accepting the result from your system. I'd
like to explore more possibilities before we settle on a solution.

Perhaps I should just add checks to assume an unspecified time zone wrt
output formatting if the tm_isdst flag comes back as "-1"?

I'll have to look at the ramifications for input times and for
dump/restore operations. Does you system respect the TZ or PGTZ
environment variable?
                        - Thomas

Thomas Lockhart writes:> > Here is the program.  The call to localtime(&t_ago) is redundant> > and hence the adjustment
oft_ago can be skipped.  It is in this> > program as a sanity check.> > As it stands, this program assumes that the
inputand resulting> > date are in the usual UNIX range of [1901, 2038].  I presume that> > there is code in place that
checksthe range of dates.> > Interesting idea. I'm not sure that assuming that timezones from> 1943 are the same as
timezonesfrom 2013 (they are not, at least in> the US) is any more valid than just accepting the result from your>
system.I'd like to explore more possibilities before we settle on> a solution.
 

As far as AIX and IRIX are concerned the timezones _are_ the same.  No
variation of rules from year to year is possible.  You are not going
to work out DST rules for earlier years without incorporating third
party libraries.  As I understand it PostgreSQL undertakes to
calculate dates only as accurately as the underlying OS allows.

The result of mktime for year < 1970 is always -1 and the struct tm is
not normalized; tm_isdst, tm_wday and tm_yday are not calculated.  I
can't see that being a useful result.
> Perhaps I should just add checks to assume an unspecified time zone> wrt output formatting if the tm_isdst flag comes
backas "-1"?
 

I'm letting the system functions worry about applying time zone and
DST offsets.  At no point do I use tm_isdst, except to set it to and
test it for -1.

Thinking about that a bit more, I think that tm_isdst should not be
written into.  It would be better to set, say, tm_wday to -1 and
change the test for failure to (t_ago == -1 && local.tm_wday == -1).
tm_isdst should be left as 0 or 1 to help out around the times of
transition to or from DST.
> I'll have to look at the ramifications for input times and for> dump/restore operations. Does you system respect the
TZor PGTZ> environment variable?
 

My code uses localtime and mktime which depend on TZ.  There is no
dependency on PGTZ, unless somewhere else in postgres there is an
equivalent of setenv(TZ=getenv(PGTZ)).
-- 
Pete Forman                 -./\.- Disclaimer: This post is originated
WesternGeco                   -./\.-  by myself and does not represent
pete.forman@westerngeco.com     -./\.-  opinion of Schlumberger, Baker
http://www.crosswinds.net/~petef  -./\.-  Hughes or their divisions.

Pete Forman <pete.forman@westerngeco.com> writes:
> Thinking about that a bit more, I think that tm_isdst should not be
> written into.

IIRC, setting isdst to -1 was necessary to get the right behavior across
DST boundaries on more-mainstream systems.  I do not think it's
acceptable to do worse on systems with good time libraries in order to
improve behavior on fundamentally broken ones.
        regards, tom lane

> As far as AIX and IRIX are concerned the timezones _are_ the same.  No
> variation of rules from year to year is possible.  You are not going
> to work out DST rules for earlier years without incorporating third
> party libraries.  As I understand it PostgreSQL undertakes to
> calculate dates only as accurately as the underlying OS allows.

Yes. Hence my reluctance to have code which does time-shifting to get
time zones for all platforms. Perhaps it could/should be a configure
test? And then we can have a "HAVE_SIMPLISTIC_TZ" (or whatever) #define
in the code to enable hacks around the problem? 

The concern led to my suggestion that we should omit timezone fields
from output -- basically do the equivalent of pre-1901 handling using
GMT -- if DST is not resolved correctly (but I'm still not sure if this
will pan out).

>  > I'll have to look at the ramifications for input times and for
>  > dump/restore operations. Does you system respect the TZ or PGTZ
>  > environment variable?
> My code uses localtime and mktime which depend on TZ.  There is no
> dependency on PGTZ, unless somewhere else in postgres there is an
> equivalent of setenv(TZ=getenv(PGTZ)).

Yes there is.
                  - Thomas

Tom Lane writes:> Pete Forman <pete.forman@westerngeco.com> writes:> > Thinking about that a bit more, I think that
tm_isdstshould not> > be written into.> > IIRC, setting isdst to -1 was necessary to get the right behavior> across DST
boundarieson more-mainstream systems.  I do not think> it's acceptable to do worse on systems with good time libraries
in>order to improve behavior on fundamentally broken ones.
 

A footnote in the C89 (and C99) standard says:
   Thus, a positive or zero value for  tm_isdst  causes  the   mktime function to presume initially that Daylight
Saving  Time, respectively, is  or  is  not  in  effect  for  the   specified time.  A negative value causes it to
attemptto   determine whether Daylight Saving Time is in  effect  for   the specified time.
 

So tm_isdst being input as 0 or 1 is not forcing the choice of what it
will be on output.  It can be important at the end of DST when local
times repeat and the only way to distinguish them is the setting of
this flag.

That is borne out by my observations.

Setting tm_isdst to -1 before calling mktime can make a difference to
the result when the input and result have different DST flags.

It is fairly arbitrary what the answer to this question is: if six
months is subtracted from a to give b, should a.local.hour =
b.local.hour or should a.utc.hour = b.utc.hour?  If you want the
former then set tm_isdst = -1 before calling mktime.  I'm out of time
now but I'll try and look for some guidance in the SQL standards.
-- 
Pete Forman                 -./\.- Disclaimer: This post is originated
WesternGeco                   -./\.-  by myself and does not represent
pete.forman@westerngeco.com     -./\.-  opinion of Schlumberger, Baker
http://www.crosswinds.net/~petef  -./\.-  Hughes or their divisions.

Pete Forman <pete.forman@westerngeco.com> writes:
> It is fairly arbitrary what the answer to this question is: if six
> months is subtracted from a to give b, should a.local.hour =
> b.local.hour or should a.utc.hour = b.utc.hour?  If you want the
> former then set tm_isdst = -1 before calling mktime.

It's not arbitrary: we want the former.  Anything else generates
bug reports from people who expect "9/1/2000 - six months" to
produce "3/1/2000", not sometime late in the evening of 2/29/2000.
        regards, tom lane