Re: Strange results with date/interval arithmetic - Mailing list pgsql-hackers

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 :-(

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.

Fixing this causes the horology regress tests to change, apparently
with good reason.  I would say that
'Wed Feb 28 17:32:01 1996 PST'::timestamptz + interval '1 year'

is more nearly Fri Feb 28 17:32:01 1997 PST than
Thu Feb 27 17:32:01 1997 PST (currently enshrined in the expected
results).  However I have not gone through all the diffs to verify each.

Thomas, you said you had additional horology tests to commit; since
we are going to have to fix and resync the horology files anyway,
do you want to go ahead and add them?

Another question: do we bump catversion and force an initdb for our
long-suffering beta testers, just to adjust two pg_proc entries?
We may not have much choice.

Sigh.  RC1 is off again.
        regards, tom lane

*** src/backend/utils/adt/timestamp.c~    Sat Dec 29 19:48:03 2001
--- src/backend/utils/adt/timestamp.c    Tue Jan  8 16:55:50 2002
***************
*** 1290,1296 **** }  
! /* timestamp_pl_span()  * Add a interval to a timestamp with time zone data type.  * Note that interval has
provisionsfor qualitative year/month  *    units, so try to do the right thing with them.
 
--- 1290,1296 ---- }  
! /* timestamptz_pl_span()  * Add a interval to a timestamp with time zone data type.  * Note that interval has
provisionsfor qualitative year/month  *    units, so try to do the right thing with them.
 
***************
*** 1371,1377 ****     tspan.month = -span->month;     tspan.time = -span->time; 
!     return DirectFunctionCall2(timestamp_pl_span,                                TimestampGetDatum(timestamp),
                       PointerGetDatum(&tspan)); }
 
--- 1371,1377 ----     tspan.month = -span->month;     tspan.time = -span->time; 
!     return DirectFunctionCall2(timestamptz_pl_span,                                TimestampGetDatum(timestamp),
                         PointerGetDatum(&tspan)); }
 
*** src/include/catalog/pg_proc.h~    Mon Nov  5 14:44:24 2001
--- src/include/catalog/pg_proc.h    Tue Jan  8 17:09:38 2002
***************
*** 1458,1466 ****  DATA(insert OID = 1188 (  timestamptz_mi   PGUID 12 f t t t 2 f 1186 "1184 1184" 100 0 0 100
timestamp_mi- )); DESCR("subtract");
 
! DATA(insert OID = 1189 (  timestamptz_pl_span PGUID 12 f t t t 2 f 1184 "1184 1186" 100 0 0 100  timestamp_pl_span -
));DESCR("plus");
 
! DATA(insert OID = 1190 (  timestamptz_mi_span PGUID 12 f t t t 2 f 1184 "1184 1186" 100 0 0 100  timestamp_mi_span -
));DESCR("minus"); DATA(insert OID = 1191 (  timestamptz        PGUID 12 f t f t 1 f 1184 "25" 100 0 0 100
text_timestamptz- )); DESCR("convert text to timestamp with time zone");
 
--- 1458,1466 ----  DATA(insert OID = 1188 (  timestamptz_mi   PGUID 12 f t t t 2 f 1186 "1184 1184" 100 0 0 100
timestamp_mi- )); DESCR("subtract");
 
! DATA(insert OID = 1189 (  timestamptz_pl_span PGUID 12 f t t t 2 f 1184 "1184 1186" 100 0 0 100  timestamptz_pl_span
-)); DESCR("plus");
 
! DATA(insert OID = 1190 (  timestamptz_mi_span PGUID 12 f t t t 2 f 1184 "1184 1186" 100 0 0 100  timestamptz_mi_span
-)); DESCR("minus"); DATA(insert OID = 1191 (  timestamptz        PGUID 12 f t f t 1 f 1184 "25" 100 0 0 100
text_timestamptz- )); DESCR("convert text to timestamp with time zone");