Thread: AW: Re: 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 ;)

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