On Jun 23, 2006, at 9:47 , Michael Glaesemann wrote:
# select '41 mon'::interval / 10;
?column?
------------------------
4 mons 2 days 24:00:00
My understanding is that as month_remainder is a float (as is
month_remainder_days), month_remainder_days may be equal to 24
hours after rounding. As we're converting from months to days, and
from days to time, rather than from months to time directly, we're
assuming that we should only have time less than 24 hours remaining
in the month_remainder_days when it's added to day_remainder.
This behavior is the same as applying justify_hours before adding the
days and time components to the result. With this in mind, I rewrote
interval_div to call interval_justify_hours. Good news is that --
enable-integer-datetimes works as expected. Bad news is that without
--enable-integer-datetimes, still get the current behavior.
I also came across something I think is odd:
# select version();
version
------------------------------------------------------------------------
----------------------------------------------------------------------
PostgreSQL 8.1.4 on powerpc-apple-darwin8.7.0, compiled by GCC
powerpc-apple-darwin8-gcc-4.0.1 (GCC) 4.0.1 (Apple Computer, Inc.
build 5341)
(1 row)
select justify_hours(a/10) as divided_and_justified
, justify_hours(b) as justified
, a/10 = b as are_equal
from (select '41 mon'::interval,'4 mons 2 days 24:00:00'::interval)
as s(a,b);
without --enable-integer-datetimes:
divided_and_justified | justified | are_equal
------------------------+---------------+-----------
4 mons 2 days 24:00:00 | 4 mons 3 days | t
(1 row)
with --enable-integer-datetimes:
divided_and_justified | justified | are_equal
-----------------------+---------------+-----------
4 mons 3 days | 4 mons 3 days | t
I think this just confirms that there is some kind of rounding (or
lack of) in interval_div. Kind of frustrating that it's not visible
in the result.
Anyway, there's another data point.
Michael Glaesemann
grzm seespotcode net
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
