From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> |
Cc: | PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>, Thomas Lockhart <lockhart(at)fourpalms(dot)org> |
Subject: | Re: Strange results with date/interval arithmetic |
Date: | 2002-01-08 22:28:41 |
Message-ID: | 18834.1010528921@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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");
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 2002-01-08 22:36:01 | Re: Strange results with date/interval arithmetic |
Previous Message | Thomas Swan | 2002-01-08 22:19:54 | Feature Request: DROP ALL FROM DATABASE database_name |