Re: Strange results with date/interval arithmetic

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)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 provisions for 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 provisions for 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");