On Sep 1, 2006, at 5:05 , Bruce Momjian wrote:
Tom Lane wrote:
Bruce Momjian <[EMAIL PROTECTED]> writes:
Well, the patch only multiplies by 30, so the interval would have to
span +5 million years to overflow. I don't see any reason to add
rounding until we get an actual query that needs it
Have you tried your patch against the various cases that have been
discussed in the past? In particular there were several distinct
examples of this behavior posted at the beginning of the thread, and
I'd not assume that a fix for one handles them all.
Yes, it fixes all posted examples, except one that displays 23:60. I
cannot reproduce that failure from Powerpc so am waiting for
Michael to
test it.
Here's your patch tested on my machine, both with and without --
enable-integer-datetimes. I've tweaked the ad hoc test suite to
include a case where the days and time differ in sign and added a
couple of queries to the ad hoc test suite to include the problems
Tom referred to--not that this patch will fix them, but to keep the
known problems together. I hope to add more to this to test more edge
cases.
Unfortunately the problem still occur (see product_d), and --enable-
integer-datetimes is pretty broken with this patch.
Michael Glaesemann
grzm seespotcode net
-- test queries
select interval '41 mon 12 days 360:00' * 0.3 as product_a
, interval '-41 mon -12 days +360:00' * 0.3 as product_b
, interval '-41 mon 12 days 360:00' * 0.3 as product_c
, interval '-41 mon -12 days -360:00' * 0.3 as product_d;
select interval '41 mon 12 days 360:00' / 10 as quotient_a
, interval '-41 mon -12 days +360:00' / 10 as quotient_b
, interval '-41 mon 12 days 360:00' / 10 as quotient_c
, interval '-41 mon -12 days -360:00' / 10 as quotient_d;
select interval '-12 days' * 0.3;
select 10000 * '1000000 hours'::interval as "ten billion";
set time zone 'EST5EDT';
select '2005-10-29 13:22:00-04'::timestamptz + '1 day'::interval as
"2005-01-30 13:22:00-05";
select '2005-10-30 13:22:00-05'::timestamptz - '2005-10-29
13:22:00-04'::timestamptz as "a day";
set time zone local;
-- end test queries
-- without --enable-integer-datetimes
select interval '41 mon 12 days 360:00' * 0.3 as product_a
, interval '-41 mon -12 days +360:00' * 0.3 as product_b
, interval '-41 mon 12 days 360:00' * 0.3 as product_c
, interval '-41 mon -12 days -360:00' * 0.3 as product_d;
product_a | product_b |
product_c | product_d
--------------------------+-----------------------------
+----------------------------+---------------------------------
1 year 11 days 146:24:00 | -1 years -11 days +69:36:00 | -1 years -5
days +98:24:00 | -1 years -11 days -146:23:60.00
(1 row)
select interval '41 mon 12 days 360:00' / 10 as quotient_a
, interval '-41 mon -12 days +360:00' / 10 as quotient_b
, interval '-41 mon 12 days 360:00' / 10 as quotient_c
, interval '-41 mon -12 days -360:00' / 10 as quotient_d;
quotient_a | quotient_b |
quotient_c | quotient_d
------------------------+---------------------------
+---------------------------+---------------------------
4 mons 4 days 40:48:00 | -4 mons -4 days +31:12:00 | -4 mons -2 days
+40:48:00 | -4 mons -4 days -40:48:00
(1 row)
select interval '-12 days' * 0.3;
?column?
----------------------
-3 days -14:23:60.00
(1 row)
select 10000 * '1000000 hours'::interval as "ten billion";
ten billion
------------------
2147483647:00:00
(1 row)
set time zone 'EST5EDT';
SET
select '2005-10-29 13:22:00-04'::timestamptz + '1 day'::interval as
"2005-01-30 13:22:00-05";
2005-01-30 13:22:00-05
------------------------
2005-10-30 13:22:00-05
(1 row)
select '2005-10-30 13:22:00-05'::timestamptz - '2005-10-29
13:22:00-04'::timestamptz as "a day";
a day
----------------
1 day 01:00:00
(1 row)
set time zone local;
SET
-- with --enable-integer-datetimes
select interval '41 mon 12 days 360:00' * 0.3 as product_a
, interval '-41 mon -12 days +360:00' * 0.3 as product_b
, interval '-41 mon 12 days 360:00' * 0.3 as product_c
, interval '-41 mon -12 days -360:00' * 0.3 as product_d;
product_a | product_b |
product_c | product_d
--------------------------+-----------------------------
+----------------------------+------------------------------
1 year 11 days 146:24:00 | -1 years -11 days +69:36:00 | -1 years -5
days +98:24:00 | -1 years -11 days -146:24:00
(1 row)
select interval '41 mon 12 days 360:00' / 10 as quotient_a
, interval '-41 mon -12 days +360:00' / 10 as quotient_b
, interval '-41 mon 12 days 360:00' / 10 as quotient_c
, interval '-41 mon -12 days -360:00' / 10 as quotient_d;
quotient_a | quotient_b |
quotient_c | quotient_d
------------------------+---------------------------
+---------------------------+---------------------------
4 mons 4 days 40:48:00 | -4 mons -4 days +31:12:00 | -4 mons -2 days
+40:48:00 | -4 mons -4 days -40:48:00
(1 row)
select interval '-12 days' * 0.3;
?column?
-------------------
-3 days -14:24:00
(1 row)
select 10000 * '1000000 hours'::interval as "ten billion";
ten billion
------------------
-00:00:00.000001
(1 row)
set time zone 'EST5EDT';
SET
select '2005-10-29 13:22:00-04'::timestamptz + '1 day'::interval as
"2005-01-30 13:22:00-05";
2005-01-30 13:22:00-05
------------------------
2005-10-30 13:22:00-05
(1 row)
select '2005-10-30 13:22:00-05'::timestamptz - '2005-10-29
13:22:00-04'::timestamptz as "a day";
a day
----------------
1 day 01:00:00
(1 row)
set time zone local;
SET
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
