tinterval - operator problems on AIX

On AIX timestamp and horology regression fails in current, because
timestamp - interval for result timestamps that are before 1970 (epoch ?)
are off by one hour. I think this is not an issue for an adapted expected file,
but a new (in 7.1beta) bug. But I am at no means an expert at what the result
should be when substracting 34 years from epoch or 'Mon Dec 30 17:32:01 1996 PST'.

Hi Andreas. It is certainly true that the behavior has changed for the
new release, but afaik it can not be put into the "bug" category.

The difference is that, before, I rotated date/time into the correct
time zone for arithmetic by adding/subtracting the current time zone.
For (date/time)-(interval), this resulted in evaluating the result in
the same time zone as the starting date/time, which was not correct.

The time zone is now evaluated in the time zone of the result, rather
than the input, using system support routines from libc. But that will
expose troubles on some platforms with time zone support before 1970.
Some platforms are worse than others; my experience has been that
zinc-based systems such as Linux, FreeBSD, and Tru64 are pretty good,
Suns are the best, and, well, apparently AIX is not ;)

- Thomas

On AIX timestamp and horology regression fails in current, because
timestamp - interval for result timestamps that are before

1970 (epoch ?)

are off by one hour. I think this is not an issue for an

adapted expected file,

but a new (in 7.1beta) bug. But I am at no means an expert

at what the result

should be when substracting 34 years from epoch or 'Mon Dec

30 17:32:01 1996 PST'.

Hi Andreas. It is certainly true that the behavior has changed for the
new release, but afaik it can not be put into the "bug" category.

The difference is that, before, I rotated date/time into the correct
time zone for arithmetic by adding/subtracting the current time zone.
For (date/time)-(interval), this resulted in evaluating the result in
the same time zone as the starting date/time, which was not correct.

The time zone is now evaluated in the time zone of the result, rather
than the input, using system support routines from libc. But that will
expose troubles on some platforms with time zone support before 1970.
Some platforms are worse than others; my experience has been that
zinc-based systems such as Linux, FreeBSD, and Tru64 are pretty good,
Suns are the best, and, well, apparently AIX is not ;)

What I do not understand is, that I thought a time in december cannot be
affected by such math because it is clearly not in the daylight savings
months of the year ? Also I thought that you recognize daylight savings time
by the PDT instead of PST, and PostgreSQL shows a result with PST as I
would have expected, but one hour off.

e.g.: Mon Dec 30 17:32:01 1996 PST - @ 34 years --> Sun Dec 30 16:32:01 1962 PST

How do we go about resolving this ? I hesitate to mark a bogus result
"ok".

Andreas

What I do not understand is, that I thought a time in december cannot be
affected by such math because it is clearly not in the daylight savings
months of the year ? Also I thought that you recognize daylight savings time
by the PDT instead of PST, and PostgreSQL shows a result with PST as I
would have expected, but one hour off.

By the time the math is actually done, the date is in GMT. So the issue
is: how does one assign the time zone for the result? Or more properly,
how does one account for time zone shifts (e.g. Daylight time vs
Standard time) when doing date/time arithmetic? The previous scheme
rotated the date/time by the same amount as the original date input. The
current scheme uses your system's date/time support functions to deduce
the correct time zone and DST setting once the math is complete. In
principle, this gives your system the opportunity to come up with the
correct answer, where previously the answer was not what might be
expected for any platform.

e.g.: Mon Dec 30 17:32:01 1996 PST - @ 34 years --> Sun Dec 30 16:32:01 1962 PST
How do we go about resolving this ? I hesitate to mark a bogus result
"ok".

The result must be what the AIX developers want ;)

I've made some assumptions in this conversation:

1) Some platforms, Linux included, give "correct results". I think I
checked this thoroughly, but, well, you know how that goes sometimes...

2) You are diff'ing the current "expected" against current "results",
and not a previous version of "expected".

3) Some platforms do not do the right thing wrt time zones for dates
before 1970. For example, the USA had some weird time zones during WWII,
and this is reflected in Sun's time zone database but not quite as
accurately in the open source zinc package that Linux and others use.
And other platforms (such as AIX?) prefer to do it themselves and get it
wrong altogether.

- Thomas

On Tue, Jan 09, 2001 at 05:05:34PM +0000, Thomas Lockhart wrote:

What I do not understand is, that I thought a time in december cannot be
affected by such math because it is clearly not in the daylight savings
months of the year ? Also I thought that you recognize daylight savings
time by the PDT instead of PST, and PostgreSQL shows a result with PST
as I would have expected, but one hour off.

By the time the math is actually done, the date is in GMT. So the issue
is: how does one assign the time zone for the result?
...
3) Some platforms do not do the right thing wrt time zones for dates
before 1970. For example, the USA had some weird time zones during WWII,
and this is reflected in Sun's time zone database but not quite as
accurately in the open source zinc package that Linux and others use.
And other platforms (such as AIX?) prefer to do it themselves and get it
wrong altogether.

Indeed, Unix time_t is not required to represent any time before 1970,
so it may be that on AIX the time zone computation is treating it as
a very large unsigned (therefore positive) value, coming up with a time
in the summer of (perhaps) 2098, and adjusting the time accordingly;
then feeding the time to a different conversion that treats negative
time_t values as really negative.

Of course, it is a grievous error to use Unix time_t to represent
real-world dates (e.g. birthdates). Didn't Y2K teach us anything?
ISTM the correct response is to remove test cases from the regression
suite that refer to times before 1970 and after 2038, if they involve
a time_t representation.

Nathan Myers
ncm@zembu.com

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 of
type time_t. If the time cannot be 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 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.

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.

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?

It is obviously not possible to determine tm_isdst with mktime for a
negative time_t. Thus with above fix PST works, but PDT is then busted :-(
localtime does convert a negative time_t correctly including dst.
Is there another way to determine tm_isdst ?

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?

It is obviously not possible to determine tm_isdst with mktime for a
negative time_t. Thus with above fix PST works, but PDT is then busted :-(

Obvious to AIX only? My conclusion is that the AIX timezone database is
damaged or missing for pre-1970 dates, but that other systems bothered
to get it at least somewhat right. Is there another issue here that I'm
missing?

localtime does convert a negative time_t correctly including dst.
Is there another way to determine tm_isdst ?

Yes. Replace AIX with Linux or something else, then recompile Postgres
;)

Seriously, not that I know of. The problem is that there is no API for
accessing time zone info other than the localtime()/mktime() kinds of
calls, so we are stuck using that (or stuck repackaging something like
zinc into Postgres directly, which afaik is not anything we would be
interested in doing).

- Thomas

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?

It is obviously not possible to determine tm_isdst with mktime for a
negative time_t. Thus with above fix PST works, but PDT is then busted :-(

Obvious to AIX only?

Yes. The whole subject only concerns AIX (at least so far).

My conclusion is that the AIX timezone database is
damaged or missing for pre-1970 dates, but that other systems bothered
to get it at least somewhat right. Is there another issue here that I'm
missing?

The tz db is neighter damaged nor missing anything (see below). Only mktime
does not work for some (maybe even avoidable) reason for dates before 1970.

localtime does convert a negative time_t correctly including dst.
Is there another way to determine tm_isdst ?

Yes. Replace AIX with Linux or something else, then recompile Postgres
;)

As I see it, the Linux results are also not 100 % correct in respect to dates
before 1970. (based on assumption that Solaris is correct)

e.g.:
1503c1503
< | Sat May 10 23:59:12 1947 PST
---

| Sat May 10 23:59:12 1947 PDT

Was 1947 PDT or PST ? In eighter case one result is one hour off, Solaris or Linux.

This raises another issue. Why do we distribute expected files with bogus results
in them ? Imho it would be better to only have expected files for rounding issues and
the like. Else the user feels that horology works fine on his machine, but as it looks it only
works on a few.

Andreas

Zeugswetter Andreas SB writes:

Try the attachment with negative values, and tell us whether mktime
returns anything other that -1. Do you have an idea how else we
could determine daylight savings time ?

mktime always returns -1 for tm's that might expect to return a
negative number. In those cases the tm is not normalized and
tm_isdst is set to -1. When mktime returns zero or positive then tm
is normalized and tm_isdst is set to 0 or 1.

localtime sets all the fields of tm correctly, including tm_isdst, for
all values of time_t, including negative ones. When I say correctly,
there is the usual limitation that the rules to specify when DST is in
force cannot express a variation from year to year. (You can specify
e.g. the last Sunday in a month.)

My observations were consistent across AIX 4.1.5, 4.2.1, and 4.3.3.

If you have a time_t, then you can use localtime to determine DST. If
you have a tm then you cannot work out DST for dates before the epoch.
One workaround would be to add 4*n to tm_year and subtract (365*4+1)
*24*60*60*n from the time_t returned. (All leap years are multiples
of 4 in the range 1901 to 2038. If tm_wday is wanted, that will need
to be adjusted as well.) But don't you do time interval arithmetic
using PostgreSQL date types rather than accepting the limitations of
POSIX/UNIX?
--
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 writes:

One workaround would be to add 4*n to tm_year and subtract (365*4+1)
*24*60*60*n from the time_t returned. (All leap years are multiples
of 4 in the range 1901 to 2038. If tm_wday is wanted, that will need
to be adjusted as well.)

FWIW, that should be to add 28*n to tm_year and subtract (365*4+1)*7
*24*60*60*n from the time_t returned. That calculates tm_wday
correctly.

Also I should have been more explicit that this applies only to AIX
and IRIX. Those return -1 from mktime(year < 1970) and do not allow
DST rules to vary from year to year. Linux and Solaris have more
capable date libraries.
--
Pete Forman http://www.bedford.waii.com/wsdev/petef/PeteF_links.html
WesternGeco http://www.crosswinds.net/~petef
Manton Lane, Bedford, mailto:pete.forman@westerngeco.com
MK41 7PA, UK tel:+44-1234-224798 fax:+44-1234-224804

As I see it, the Linux results are also not 100 % correct in respect to dates
before 1970. (based on assumption that Solaris is correct)
< | Sat May 10 23:59:12 1947 PST

| Sat May 10 23:59:12 1947 PDT

Was 1947 PDT or PST ? In eighter case one result is one hour off, Solaris or Linux.

Yes, I've seen this before. As I mentioned, Solaris does a particularly
good job of including the variations in DST definitions around WWII and
earlier. In the US, there are several different conventions used in
those years, presumably based on a need for energy conservation and
perhaps to maximize production efficiency.

This raises another issue. Why do we distribute expected files with bogus results
in them?

It depends on what you mean by "bogus". imho we should (and do)
distribute "expected" files which reflect the results expected for a
particular machine -- based on a careful analysis of the results from
that machine from an expert, such as you are doing with AIX. Your
results are incremental differences from some other "standard machine",
which has also been carefully analyzed. By definition, the "standard
machine" has been and is a Linux box, for the simple historical reason
that this was my machine at the time that scrappy and I resurrected the
regression tests several years ago. But it is a good choice for the
standard, since that style of machine has a large installed base and the
cost of entry for someone else wanting to participate is very low.

If I understand the alternatives you are considering, the other choice
is to distribute "expected" files which reflect what we think a machine
should produce if it behaved the way we think it should. That doesn't
really help anyone, since a tester would have to derive from first
principles the correctness of the test results each time they run the
test.

Instead, we document the current behavior, and the regression tests can
now catch *changes* in behavior, to be analyzed just as you are doing.
If AIX fixes their mktime() and timezone behavior, you (or someone else
running AIX) will see it, evaluate it, and adjust the regression
"expected" files to reflect this.

- Thomas

FWIW, that should be to add 28*n to tm_year and subtract (365*4+1)*7
*24*60*60*n from the time_t returned. That calculates tm_wday
correctly.
Also I should have been more explicit that this applies only to AIX
and IRIX. Those return -1 from mktime(year < 1970) and do not allow
DST rules to vary from year to year. Linux and Solaris have more
capable date libraries.

Oh, so AIX and IRIX have just one-line time zone databases? Yuck.

How about having some #if BROKEN_TIMEZONE_DATABASE code which uses both
mktime() and localtime() to derive the correct time zone? That is, call
mktime to get a time_t, then call localtime() to get the time zone info,
but only on platforms which do not get a complete result from just the
call to mktime(). In fact, we *could* check for tm->tm_isdst coming back
"-1" for every platform, then call localtime() to make a last stab at
getting a good value.

Comments?

- Thomas

Oh, so AIX and IRIX have just one-line time zone databases? Yuck.

How about having some #if BROKEN_TIMEZONE_DATABASE code which uses both
mktime() and localtime() to derive the correct time zone? That is, call
mktime to get a time_t, then call localtime() to get the time zone info,
but only on platforms which do not get a complete result from just the
call to mktime(). In fact, we *could* check for tm->tm_isdst coming back
"-1" for every platform, then call localtime() to make a last stab at
getting a good value.

How would we construct a valid time_t from the struct tm without mktime?

Andreas

How about having some #if BROKEN_TIMEZONE_DATABASE code which uses both
mktime() and localtime() to derive the correct time zone? That is, call
mktime to get a time_t, then call localtime() to get the time zone info,
but only on platforms which do not get a complete result from just the
call to mktime(). In fact, we *could* check for tm->tm_isdst coming back
"-1" for every platform, then call localtime() to make a last stab at
getting a good value.

How would we construct a valid time_t from the struct tm without mktime?

If I understand the info you have given previously, it should be
possible to get a valid tm->tm_isdst by the following sequence of calls:

// call mktime() which might return a "-1" for DST
time = mktime(tm);
// time is now a correct GMT time
// localtime() *is* allowed to return a good tm->tm_isdst
// even for "negative" time_t values.
// I thought I understood this from Andreas' info...
newtm = localtime(time);
// use the new flag for something useful...
dstflag = newtm->tm_isdst;

Yes?

- Thomas

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

How about having some #if BROKEN_TIMEZONE_DATABASE code which uses both
mktime() and localtime() to derive the correct time zone? That is, call
mktime to get a time_t, then call localtime() to get the time zone info,
but only on platforms which do not get a complete result from just the
call to mktime(). In fact, we *could* check for tm->tm_isdst coming back
"-1" for every platform, then call localtime() to make a last stab at
getting a good value.

How would we construct a valid time_t from the struct tm

without mktime?

If I understand the info you have given previously, it should be
possible to get a valid tm->tm_isdst by the following
sequence of calls:

// call mktime() which might return a "-1" for DST
time = mktime(tm);
// time is now a correct GMT time

Unfortunately the returned time is -1 for all dates before 1970
(on AIX and, as I understood, IRIX) :-(
(can you send someone from IBM, that I can shout at, to releive my anger)

Andreas

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.