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
(1 row)
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
(1 row)
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, Pennsylvania 19026Import Notes
Reply to msg id not found:
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 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");
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, Pennsylvania 19026Bruce 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, Pennsylvania 19026