Re: Re: PostgreSQL 7.0.2 Date Miscalculation

Tom Lane wrote:
>
> Thomas Lockhart <lockhart(at)alumni(dot)caltech(dot)edu> writes:
> >> --- ./results/horology.out Mon Apr 2 17:06:59 2001
> >> SELECT time with time zone '01:30' + interval '02:01' AS "03:31:00-08";
> >> 03:31:00-08
> >> -------------
> >> ! 03:31:00-07
>
> > Hmm. This is just a badly designed regression test (I can say that,
> > since it is probably mine ;)
>
> > I was trying to exercise TIME WITH TIME ZONE with the *implicit* time
> > zone for today. That really won't work in a testable way, since the
> > result varies during the year :(
> What I'm curious about is why I'm not seeing a failure on HPUX. If your
> explanation is right then this test should fail everywhere during
> daylight savings season.

Well, we won't hold up HPUX as a model for "standard behavior", eh? But
I'm not sure why you don't see the behavior. afaik the calculations
involved should be something like (haven't looked it up, but...):

1) interpret TIME WITH TIME ZONE '01:30' as the time with the time zone
appropriate for that hour today. Convert to internal representation as a
time field with an explicit numeric time zone value.

2) interpret INTERVAL '02:01' as an interval. No month/year fields, and
no time zone involved.

3) Add the interval to the time. Both are in units of seconds
internally.

4) Store the time field modulo 86400, pushing it back into a 24 hour
range. Store the time zone field from step (1) into the result.

5) Print result, using only the internal time zone offset.

- Thomas