Re: Interval aggregate regression failure (expected seems

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