Re: Strange results with date/interval arithmetic

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > I am seeing strange results from date/interval computations involving
> > months.
> 
> Ah, this is what we get for running the regression tests in only one
> time zone :-(

Yes, I realized the problem was that "2001-03-01" for +1 GMT timezone
comes out as "2001-02-28 23:00 GMT", and when you subtract a month from
that, you get "2001-01-28 23:00" and adding the +1 timezone gives you
"2001-01-29" which is not what you expected.

> The problem appears to be cut-and-paste errors in timestamp.c and
> pg_proc.h: various things that should be timestamp are timestamptz
> or vice versa.  See attached proposed patches.

Oh, so that is why the difference between timestamp and timestamptz is
so important.

> long-suffering beta testers, just to adjust two pg_proc entries?
> We may not have much choice.
> 
> Sigh.  RC1 is off again.

Ouch.  :-)  

At least it is before final.  Can we give people on hackers an SQL
script to run in every database to fix this?  That is how we have
handled this in the past.  Perhaps we can update the catversion as part
of the patch too.  (We have never done that before.)

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026