Re: [SQL] Interval subtracting

Attached is the new patch.  To summarize:

   - new function justify_interval(interval)
   - modified function justify_hours(interval)
   - modified function justify_days(interval)

These functions are defined to meet the requirements as discussed in this
thread.  Specifically:

   - justify_hours makes certain the sign bit on the hours
     matches the sign bit on the days.  It only checks the
     sign bit on the days, and not the months, when
     determining if the hours should be positive or negative.
     After the call, -24 < hours < 24.

   - justify_days makes certain the sign bit on the days
     matches the sign bit on the months.  It's behavior does
     not depend on the hours, nor does it modify the hours.
     After the call, -30 < days < 30.

   - justify_interval makes sure the sign bits on all three
     fields months, days, and hours are all the same.  After
     the call, -24 < hours < 24 AND -30 < days < 30.

'make check' passes all tests.  There are no tests for justify_interval, as it
is new.  But the existing tests for justify_hours and justify_days appear to
still work, even though the behavior has changed.  Apparently, their test cases
are not sensitive to the particular changes that have occurred.

I would include new tests in the patch but do not know on which reference
machine/platform the patches are supposed to be generated.

mark
Index: src/backend/utils/adt/timestamp.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/utils/adt/timestamp.c,v
retrieving revision 1.160
diff --context -r1.160 timestamp.c
*** src/backend/utils/adt/timestamp.c    22 Nov 2005 22:30:33 -0000    1.160
--- src/backend/utils/adt/timestamp.c    3 Mar 2006 20:23:26 -0000
***************
*** 1975,1980 ****
--- 1975,2054 ----
  }

  /*
+  *  interval_justify_interval()
+  *
+  *  Adjust interval so 'month', 'day', and 'time' portions are within
+  *  customary bounds.  Specifically:
+  *
+  *      0 <= abs(time) < 24 hours
+  *      0 <= abs(day)  < 30 days
+  *
+  *  Also, the sign bit on all three fields is made equal, so either
+  *  all three fields are negative or all are positive.
+  */
+ Datum
+ interval_justify_interval(PG_FUNCTION_ARGS)
+ {
+     Interval   *span = PG_GETARG_INTERVAL_P(0);
+     Interval   *result;
+
+ #ifdef HAVE_INT64_TIMESTAMP
+     int64        wholeday;
+ #else
+     double        wholeday;
+ #endif
+     int32        wholemonth;
+
+     result = (Interval *) palloc(sizeof(Interval));
+     result->month = span->month;
+     result->day = span->day;
+     result->time = span->time;
+
+ #ifdef HAVE_INT64_TIMESTAMP
+     TMODULO(result->time, wholeday, USECS_PER_DAY);
+ #else
+     TMODULO(result->time, wholeday, (double) SECS_PER_DAY);
+ #endif
+     result->day += wholeday;    /* could overflow... */
+
+     wholemonth = result->day / DAYS_PER_MONTH;
+     result->day -= wholemonth * DAYS_PER_MONTH;
+     result->month += wholemonth;
+
+     if (result->month < 0 && result->day > 0)
+     {
+         result->day -= DAYS_PER_MONTH;
+         result->month++;
+     }
+     else if (result->month > 0 && result->day < 0)
+     {
+         result->day += DAYS_PER_MONTH;
+         result->month--;
+     }
+
+     if (result->time < 0 && result->day > 0)
+     {
+ #ifdef HAVE_INT64_TIMESTAMP
+         result->time += USECS_PER_DAY;
+ #else
+         result->time += (double) SECS_PER_DAY;
+ #endif
+         result->day--;
+     }
+     else if (result->time > 0 && result->day < 0)
+     {
+ #ifdef HAVE_INT64_TIMESTAMP
+         result->time -= USECS_PER_DAY;
+ #else
+         result->time -= (double) SECS_PER_DAY;
+ #endif
+         result->day++;
+     }
+
+     PG_RETURN_INTERVAL_P(result);
+ }
+
+ /*
   *    interval_justify_hours()
   *
   *    Adjust interval so 'time' contains less than a whole day, adding
***************
*** 2006,2011 ****
--- 2080,2104 ----
  #endif
      result->day += wholeday;    /* could overflow... */

+     if (result->time < 0 && result->day > 0)
+     {
+ #ifdef HAVE_INT64_TIMESTAMP
+         result->time += USECS_PER_DAY;
+ #else
+         result->time += (double) SECS_PER_DAY;
+ #endif
+         result->day--;
+     }
+     else if (result->time > 0 && result->day < 0)
+     {
+ #ifdef HAVE_INT64_TIMESTAMP
+         result->time -= USECS_PER_DAY;
+ #else
+         result->time -= (double) SECS_PER_DAY;
+ #endif
+         result->day++;
+     }
+
      PG_RETURN_INTERVAL_P(result);
  }

***************
*** 2031,2036 ****
--- 2124,2140 ----
      result->day -= wholemonth * DAYS_PER_MONTH;
      result->month += wholemonth;

+     if (result->month < 0 && result->day > 0)
+     {
+         result->day -= DAYS_PER_MONTH;
+         result->month++;
+     }
+     else if (result->month > 0 && result->day < 0)
+     {
+         result->day += DAYS_PER_MONTH;
+         result->month--;
+     }
+
      PG_RETURN_INTERVAL_P(result);
  }

Index: src/include/catalog/pg_proc.h
===================================================================
RCS file: /projects/cvsroot/pgsql/src/include/catalog/pg_proc.h,v
retrieving revision 1.399
diff --context -r1.399 pg_proc.h
*** src/include/catalog/pg_proc.h    28 Feb 2006 22:37:26 -0000    1.399
--- src/include/catalog/pg_proc.h    3 Mar 2006 20:23:27 -0000
***************
*** 1462,1467 ****
--- 1462,1469 ----
  DESCR("convert abstime to timestamp with time zone");
  DATA(insert OID = 1174 (  timestamptz       PGNSP PGUID 12 f f t f s 1 1184 "1082" _null_ _null_ _null_
date_timestamptz- _null_ )); 
  DESCR("convert date to timestamp with time zone");
+ DATA(insert OID = 2711 (  justify_interval PGNSP PGUID 12 f f t f i 1 1186 "1186" _null_ _null_ _null_
interval_justify_interval- _null_ )); 
+ DESCR("promote groups of 24 hours to numbers of days and promote groups of 30 days to numbers of months");
  DATA(insert OID = 1175 (  justify_hours    PGNSP PGUID 12 f f t f i 1 1186 "1186" _null_ _null_ _null_
interval_justify_hours- _null_ )); 
  DESCR("promote groups of 24 hours to numbers of days");
  DATA(insert OID = 1295 (  justify_days       PGNSP PGUID 12 f f t f i 1 1186 "1186" _null_ _null_ _null_
interval_justify_days- _null_ )); 
Index: src/include/utils/timestamp.h
===================================================================
RCS file: /projects/cvsroot/pgsql/src/include/utils/timestamp.h,v
retrieving revision 1.57
diff --context -r1.57 timestamp.h
*** src/include/utils/timestamp.h    15 Oct 2005 02:49:46 -0000    1.57
--- src/include/utils/timestamp.h    3 Mar 2006 20:23:27 -0000
***************
*** 234,239 ****
--- 234,240 ----
  extern Datum interval_hash(PG_FUNCTION_ARGS);
  extern Datum interval_smaller(PG_FUNCTION_ARGS);
  extern Datum interval_larger(PG_FUNCTION_ARGS);
+ extern Datum interval_justify_interval(PG_FUNCTION_ARGS);
  extern Datum interval_justify_hours(PG_FUNCTION_ARGS);
  extern Datum interval_justify_days(PG_FUNCTION_ARGS);