# Re: [PATCHES] fix integer datetime division rounding error

Andrew Dunstan wrote:
>
> The attached patch seems to fix the rounding error that is causing
> regression failures on machines with integer datetimes. (Source of error
> discovered by [EMAIL PROTECTED]).ISTM this code needs to be given some
> careful analysis - I know it makes my head spin reading it.

Ah, brilliant!  I knew I was missing something fundamental, and the use
of rint() was it.  Strangely enough, the 8.0 code uses rint() in that
function, but for floating point intervals, and the code was buggy,
generating negative time values for division.

Patch attached and applied.  I also improved the interval multiplication
code.

--
Bruce Momjian                        |  http://candle.pha.pa.us
pgman@candle.pha.pa.us               |  (610) 359-1001
+  If your life is a hard drive,     |  13 Roberts Road
+  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073
===================================================================
retrieving revision 1.145
diff -c -c -r1.145 timestamp.c
*** src/backend/utils/adt/timestamp.c   23 Jul 2005 14:53:21 -0000      1.145
--- src/backend/utils/adt/timestamp.c   24 Jul 2005 04:34:56 -0000
***************
*** 2244,2281 ****
Datum
interval_mul(PG_FUNCTION_ARGS)
{
!       Interval   *span1 = PG_GETARG_INTERVAL_P(0);
float8          factor = PG_GETARG_FLOAT8(1);
Interval   *result;

- #ifdef HAVE_INT64_TIMESTAMP
-       int64           months;
-       int64       days;
- #else
-       double          months;
-       double      days;
- #endif
-
result = (Interval *) palloc(sizeof(Interval));

!       months = span1->month * factor;
!       days = span1->day * factor;
#ifdef HAVE_INT64_TIMESTAMP
!       result->month = months;
!       result->day = days;
!       result->time = span1->time * factor;
!       result->time += (months - result->month) * INT64CONST(30) *
USECS_PER_DAY;
!       result->time += (days - result->day) * INT64CONST(24) * USECS_PER_HOUR;
! #else
!       result->month = (int)months;
!       result->day = (int)days;
!       result->time = JROUND(span1->time * factor);
!       /* evaluate fractional months as 30 days */
!       result->time += JROUND((months - result->month) * DAYS_PER_MONTH *
SECS_PER_DAY);
!       /* evaluate fractional days as 24 hours */
!       result->time += JROUND((days - result->day) * HOURS_PER_DAY *
SECS_PER_HOUR);
#endif

PG_RETURN_INTERVAL_P(result);
}

--- 2244,2280 ----
Datum
interval_mul(PG_FUNCTION_ARGS)
{
!       Interval   *span = PG_GETARG_INTERVAL_P(0);
float8          factor = PG_GETARG_FLOAT8(1);
+       double          month_remainder, day_remainder;
Interval   *result;

result = (Interval *) palloc(sizeof(Interval));

!       result->month = span->month * factor;
!       result->day = span->day * factor;
!
!       /* Compute remainders */
!       month_remainder = span->month * factor - result->month;
!       day_remainder = span->day * factor - result->day;
!
!       /* Cascade fractions to lower units */
!       /* fractional months full days into days */
!       result->day += month_remainder * DAYS_PER_MONTH;
!       /* fractional months partial days into time */
!       day_remainder += (month_remainder * DAYS_PER_MONTH) -
!                                        (int)(month_remainder *
DAYS_PER_MONTH);
!
#ifdef HAVE_INT64_TIMESTAMP
!       result->time = rint(span->time * factor +
!                                       day_remainder * USECS_PER_DAY);
! #else
!       result->time = JROUND(span->time * factor +
!                                       day_remainder * SECS_PER_DAY);
#endif

+       result = DatumGetIntervalP(DirectFunctionCall1(interval_justify_hours,
+
IntervalPGetDatum(result)));
PG_RETURN_INTERVAL_P(result);
}

***************
*** 2284,2292 ****
{
/* Args are float8 and Interval *, but leave them as generic Datum */
Datum           factor = PG_GETARG_DATUM(0);
!       Datum           span1 = PG_GETARG_DATUM(1);

!       return DirectFunctionCall2(interval_mul, span1, factor);
}

Datum
--- 2283,2291 ----
{
/* Args are float8 and Interval *, but leave them as generic Datum */
Datum           factor = PG_GETARG_DATUM(0);
!       Datum           span = PG_GETARG_DATUM(1);

!       return DirectFunctionCall2(interval_mul, span, factor);
}

Datum
***************
*** 2316,2325 ****
/* fractional months full days into days */
result->day += month_remainder * DAYS_PER_MONTH;
/* fractional months partial days into time */
!       day_remainder += (month_remainder * DAYS_PER_MONTH) -
(int)(month_remainder * DAYS_PER_MONTH);

#ifdef HAVE_INT64_TIMESTAMP
!       result->time += day_remainder * USECS_PER_DAY;
#else
result->time += day_remainder * SECS_PER_DAY;
result->time = JROUND(result->time);
--- 2315,2325 ----
/* fractional months full days into days */
result->day += month_remainder * DAYS_PER_MONTH;
/* fractional months partial days into time */
!       day_remainder += (month_remainder * DAYS_PER_MONTH) -
!                                        (int)(month_remainder *
DAYS_PER_MONTH);

#ifdef HAVE_INT64_TIMESTAMP
!       result->time += rint(day_remainder * USECS_PER_DAY);
#else
result->time += day_remainder * SECS_PER_DAY;
result->time = JROUND(result->time);