Обсуждение: Strange results with date/interval arithmetic
I am seeing strange results from date/interval computations involving months. I get the correct answers because I have a negative setting relative to GMT. Here are my results with TZ=EST5EDT:test=> select '2001/3/1'::date - '1 month'::interval; ?column? ------------------------2001-02-01 00:00:00-05(1 row) With GMT it is OK too: test=> select '2001/3/1'::date - '1 month'::interval; ?column? ------------------------ 2001-02-01 00:00:00+00(1row) However, with GMT+1 I see a big failure: test=> select '2001/3/1'::date - '1 month'::interval; ?column? ------------------------ 2001-01-29 00:00:00+01(1row) Why does it say 2001-01-29? This is interesting:test=> select '2001/7/1'::date - '1 month'::interval; ?column? ------------------------2001-05-31 00:00:00+02(1 row)test=> select '2001/8/1'::date - '1 month'::interval; ?column? ------------------------ 2001-07-01 00:00:00+02(1 row) Because August and July have the same number of months, it worked. I am going to research this but someone may know the solution already. -- 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
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");
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
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> 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.
We have? I don't really know how one would adjust catversion without
an initdb. (Bear in mind it's inside a binary, CRC-protected control
file; couldn't be done without a special-purpose C program AFAICS.)
If you wanted to *not* bump the catversion then we could let people run
a script to fix the two pg_proc entries, but I think that way is likely
to do more harm than good in the long run. Too much chance of someone
carrying the wrong entries into production and not noticing their wrong
answers for a long time.
The ground rules for beta testers have always been "you may have to
initdb before final", and I think that's where we are now, annoying
as it is.
(Too bad we don't have a working pg_upgrade...)
regards, tom lane
Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > 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. > > We have? I don't really know how one would adjust catversion without > an initdb. (Bear in mind it's inside a binary, CRC-protected control > file; couldn't be done without a special-purpose C program AFAICS.) > Yes, that seems like a problem. Also, will this affect regression tests for people who don't apply the patch? That makes it extra important we make sure the patch is applied which gives more weight to the catversion bump. > If you wanted to *not* bump the catversion then we could let people run > a script to fix the two pg_proc entries, but I think that way is likely > to do more harm than good in the long run. Too much chance of someone > carrying the wrong entries into production and not noticing their wrong > answers for a long time. Yes. > The ground rules for beta testers have always been "you may have to > initdb before final", and I think that's where we are now, annoying > as it is. > > (Too bad we don't have a working pg_upgrade...) I could probably get it working tomorrow if people want it. -- 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