Re: fix integer datetime division rounding error - Mailing list pgsql-patches

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 Andrew@Supernews).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
Index: src/backend/utils/adt/timestamp.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/utils/adt/timestamp.c,v
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);