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

Reply via email to