Re: Interval output bug in HAVE_INT64_TIMESTAMP

Ron Mayer wrote:
> Tom Lane wrote:
>> Yeah, bug all the way back --- applied.
>> I don't much like the forced rounding to two digits here, but changing
>> that doesn't seem like material for back-patching.  Are you going to
>> fix that up while working on your other patches?
>
> Gladly.  I hate that too.

Attached is a WIP interval code-cleanup patch that tries to reuse
more code between interval style.   Once side-benefit is that it
makes rounding more consistent (so we should have fewer bugs like
that last rounding one that only affected one date style), as well
as removing roughly 250 lines of near-copy-and-paste code (some
of it my doing in my previous patches) and IMHO making the code
easier to read.

This particular patch lives on top of the other 2 I submitted,
but I could make a similar one that doesn't depend on those
if either of those get rejected - though the savings wouldn't
be as great since some of the benefit is that all 4 datestyles
can share some of the refactored code.  It does do some
extra function calls; but I think any cost there's easily
offset by the removal of gratuitous (AFAICT) strlen()'s that
were scattered in the existing code and are now removed.

Despite the rounding differences it seems to have no effect
on the regression test output - presumably because these rounding
differences are more obscure corner cases.

I consider it a WIP for 3 reasons:

  1) Does anyone object to the not-quite-backward compatible
     rounding (forced 2 digits; now some rounding instead
     of truncation).
  2) Do we even want broad scale refactoring like this, or do
     people prefer minimal changes (like my previous patches)
     that tried to as much of the old code intact preferred.
  3) If people like this kind of refactoring, there's more
     I could do - especially if I can I find a place to put
     functions the ECPG stuff can call too.


*** a/src/backend/utils/adt/datetime.c
--- b/src/backend/utils/adt/datetime.c
***************
*** 406,421 **** static void
  TrimTrailingZeros(char *str)
  {
      int            len = strlen(str);
-
- #if 0
-     /* chop off trailing one to cope with interval rounding */
-     if (strcmp(str + len - 4, "0001") == 0)
-     {
-         len -= 4;
-         *(str + len) = '\0';
-     }
- #endif
-
      /* chop off trailing zeros... but leave at least 2 fractional digits */
      while (*(str + len - 1) == '0' && *(str + len - 3) != '.')
      {
--- 406,411 ----
***************
*** 2724,2732 **** DecodeSpecial(int field, char *lowtoken, int *val)


  /*
!  * A small helper function to avoid cut&paste code in DecodeIso8601Interval
   */
! static void adjust_fval(double fval,struct pg_tm * tm, fsec_t *fsec, int scale)
  {
      int    sec;
      if (fval == 0) return;
--- 2714,2723 ----


  /*
!  * Small helper functions to avoid cut&paste code in DecodeInterval and DecodeIso8601Interval
   */
! static void
! adjust_fractional_seconds(double fval,struct pg_tm * tm, fsec_t *fsec, int scale)
  {
      int    sec;
      if (fval == 0) return;
***************
*** 2734,2745 **** static void adjust_fval(double fval,struct pg_tm * tm, fsec_t *fsec, int scale)
      sec            = fval;
      tm->tm_sec += sec;
  #ifdef HAVE_INT64_TIMESTAMP
!     *fsec       += ((fval - sec) * 1000000);
  #else
      *fsec       += (fval - sec);
  #endif
  }


  /* DecodeISO8601Interval()
   *
--- 2725,2748 ----
      sec            = fval;
      tm->tm_sec += sec;
  #ifdef HAVE_INT64_TIMESTAMP
!     *fsec       += rint((fval - sec) * 1000000);
  #else
      *fsec       += (fval - sec);
  #endif
  }

+ static void
+ adjust_fractional_days(double fval,struct pg_tm * tm, fsec_t *fsec, int scale)
+ {
+     int    extra_days;
+     if (fval == 0) return;
+     fval *= scale;
+     extra_days = fval;
+     tm->tm_mday += extra_days;
+     fval -= extra_days;
+     adjust_fractional_seconds(fval,tm,fsec, SECS_PER_DAY);
+ }
+

  /* DecodeISO8601Interval()
   *
***************
*** 2768,2775 **** int
  DecodeISO8601Interval(char *str, struct pg_tm * tm, fsec_t *fsec)
  {
      char    unit;
-     int        fmask = 0,
-             tmask;
      int        val;
      double    fval;
      int        datepart = true;
--- 2771,2776 ----
***************
*** 2785,2798 **** DecodeISO8601Interval(char *str, struct pg_tm * tm, fsec_t *fsec)

      /*
       * An ISO 8601 "time-interval by duration only" must start
!      * with a 'P'.  If it contains a date-part, 'p' will be the
!      * only character in the field.  If it contains no date part
!      * it will contain exactly to characters 'PT' indicating a
!      * time part.
!      * Anything else does not match an ISO 8601 basic interval
!      * and will be treated like a traditional postgresql interval.
       */
!     if (!(str[0] == 'P'))
      {
          return DTERR_BAD_FORMAT;
      }
--- 2786,2795 ----

      /*
       * An ISO 8601 "time-interval by duration only" must start
!      * with a 'P' and needs to be at least 3 characters long to
!      * insure that it had a field set.
       */
!     if (strlen(str)<3 || !(str[0] == 'P'))
      {
          return DTERR_BAD_FORMAT;
      }
***************
*** 2826,2860 **** DecodeISO8601Interval(char *str, struct pg_tm * tm, fsec_t *fsec)
              {
                  case 'D':
                      tm->tm_mday += val;
!                     adjust_fval(fval,tm,fsec, 86400);
!                     tmask = ((fmask & DTK_M(DAY)) ? 0 : DTK_M(DAY));
                      break;
-
                  case 'W':
                      tm->tm_mday += val * 7;
!                     adjust_fval(fval,tm,fsec,7 * 86400);
!                     tmask = ((fmask & DTK_M(DAY)) ? 0 : DTK_M(DAY));
                      break;
-
                  case 'M':
                      tm->tm_mon += val;
!                     adjust_fval(fval,tm,fsec,30 * 86400);
!                     tmask = DTK_M(MONTH);
                      break;
-
                  case 'Y':
-                     /*
-                      * Why can fractional months produce seconds,
-                      * but fractional years can't?  Well the older
-                      * interval code below has the same property
-                      * so this one follows the other one too.
-                      */
                      tm->tm_year += val;
                      if (fval != 0)
                          tm->tm_mon += (fval * 12);
-                     tmask = ((fmask & DTK_M(YEAR)) ? 0 : DTK_M(YEAR));
                      break;
-
                  default:
                      return DTERR_BAD_FORMAT;  /* not a vald ISO8601 date unit prefix */
              }
--- 2823,2843 ----
              {
                  case 'D':
                      tm->tm_mday += val;
!                     adjust_fractional_seconds(fval,tm,fsec, SECS_PER_DAY);
                      break;
                  case 'W':
                      tm->tm_mday += val * 7;
!                     adjust_fractional_days(fval,tm,fsec,7);
                      break;
                  case 'M':
                      tm->tm_mon += val;
!                     adjust_fractional_days(fval,tm,fsec,DAYS_PER_MONTH);
                      break;
                  case 'Y':
                      tm->tm_year += val;
                      if (fval != 0)
                          tm->tm_mon += (fval * 12);
                      break;
                  default:
                      return DTERR_BAD_FORMAT;  /* not a vald ISO8601 date unit prefix */
              }
***************
*** 2865,2902 **** DecodeISO8601Interval(char *str, struct pg_tm * tm, fsec_t *fsec)
              {
                  case 'S':
                      tm->tm_sec += val;
!                     adjust_fval(fval,tm,fsec,1);
!                     tmask = DTK_M(SECOND);
                      break;
                  case 'M':
                      tm->tm_min += val;
!                     adjust_fval(fval,tm,fsec,60);
!                     tmask = DTK_M(MINUTE);
                      break;
                  case 'H':
                      tm->tm_hour += val;
!                     adjust_fval(fval,tm,fsec,3600);
!                     tmask = DTK_M(HOUR);
                      break;
                  default:
                      return DTERR_BAD_FORMAT;  /* not a vald ISO8601 time unit prefix */
              }
          }
-         fmask |= tmask;
      }

!     if (*fsec != 0)
!     {
!         int            sec;
! #ifdef HAVE_INT64_TIMESTAMP
!         sec = (*fsec / INT64CONST(1000000));
!         *fsec -= (sec * INT64CONST(1000000));
! #else
!         TMODULO(*fsec, sec, 1e0);
! #endif
!         tm->tm_sec += sec;
!     }
!     return (fmask != 0) ? 0 : -1;
  }


--- 2848,2870 ----
              {
                  case 'S':
                      tm->tm_sec += val;
!                     adjust_fractional_seconds(fval,tm,fsec,1);
                      break;
                  case 'M':
                      tm->tm_min += val;
!                     adjust_fractional_seconds(fval,tm,fsec,SECS_PER_MINUTE);
                      break;
                  case 'H':
                      tm->tm_hour += val;
!                     adjust_fractional_seconds(fval,tm,fsec,SECS_PER_HOUR);
                      break;
                  default:
                      return DTERR_BAD_FORMAT;  /* not a vald ISO8601 time unit prefix */
              }
          }
      }

!     return 0;
  }


***************
*** 3069,3099 **** DecodeInterval(char **field, int *ftype, int nf, int range,
                  switch (type)
                  {
                      case DTK_MICROSEC:
! #ifdef HAVE_INT64_TIMESTAMP
!                         *fsec += rint(val + fval);
! #else
!                         *fsec += (val + fval) * 1e-6;
! #endif
                          tmask = DTK_M(MICROSECOND);
                          break;

                      case DTK_MILLISEC:
! #ifdef HAVE_INT64_TIMESTAMP
!                         *fsec += rint((val + fval) * 1000);
! #else
!                         *fsec += (val + fval) * 1e-3;
! #endif
                          tmask = DTK_M(MILLISECOND);
                          break;

                      case DTK_SECOND:
                          tm->tm_sec += val;
! #ifdef HAVE_INT64_TIMESTAMP
!                         *fsec += rint(fval * 1000000);
! #else
!                         *fsec += fval;
! #endif
!
                          /*
                           * If any subseconds were specified, consider this
                           * microsecond and millisecond input as well.
--- 3037,3054 ----
                  switch (type)
                  {
                      case DTK_MICROSEC:
!                         adjust_fractional_seconds((val + fval) * 1e-6,tm,fsec,1);
                          tmask = DTK_M(MICROSECOND);
                          break;

                      case DTK_MILLISEC:
!                         adjust_fractional_seconds((val + fval) * 1e-3,tm,fsec,1);
                          tmask = DTK_M(MILLISECOND);
                          break;

                      case DTK_SECOND:
                          tm->tm_sec += val;
!                         adjust_fractional_seconds(fval,tm,fsec,1);
                          /*
                           * If any subseconds were specified, consider this
                           * microsecond and millisecond input as well.
***************
*** 3106,3215 **** DecodeInterval(char **field, int *ftype, int nf, int range,

                      case DTK_MINUTE:
                          tm->tm_min += val;
!                         if (fval != 0)
!                         {
!                             int            sec;
!
!                             fval *= SECS_PER_MINUTE;
!                             sec = fval;
!                             tm->tm_sec += sec;
! #ifdef HAVE_INT64_TIMESTAMP
!                             *fsec += rint((fval - sec) * 1000000);
! #else
!                             *fsec += fval - sec;
! #endif
!                         }
                          tmask = DTK_M(MINUTE);
                          break;

                      case DTK_HOUR:
                          tm->tm_hour += val;
!                         if (fval != 0)
!                         {
!                             int            sec;
!
!                             fval *= SECS_PER_HOUR;
!                             sec = fval;
!                             tm->tm_sec += sec;
! #ifdef HAVE_INT64_TIMESTAMP
!                             *fsec += rint((fval - sec) * 1000000);
! #else
!                             *fsec += fval - sec;
! #endif
!                         }
                          tmask = DTK_M(HOUR);
                          type = DTK_DAY;
                          break;

                      case DTK_DAY:
                          tm->tm_mday += val;
!                         if (fval != 0)
!                         {
!                             int            sec;
!
!                             fval *= SECS_PER_DAY;
!                             sec = fval;
!                             tm->tm_sec += sec;
! #ifdef HAVE_INT64_TIMESTAMP
!                             *fsec += rint((fval - sec) * 1000000);
! #else
!                             *fsec += fval - sec;
! #endif
!                         }
                          tmask = (fmask & DTK_M(DAY)) ? 0 : DTK_M(DAY);
                          break;

                      case DTK_WEEK:
                          tm->tm_mday += val * 7;
!                         if (fval != 0)
!                         {
!                             int            extra_days;
!
!                             fval *= 7;
!                             extra_days = (int32) fval;
!                             tm->tm_mday += extra_days;
!                             fval -= extra_days;
!                             if (fval != 0)
!                             {
!                                 int            sec;
!
!                                 fval *= SECS_PER_DAY;
!                                 sec = fval;
!                                 tm->tm_sec += sec;
! #ifdef HAVE_INT64_TIMESTAMP
!                                 *fsec += rint((fval - sec) * 1000000);
! #else
!                                 *fsec += fval - sec;
! #endif
!                             }
!                         }
                          tmask = (fmask & DTK_M(DAY)) ? 0 : DTK_M(DAY);
                          break;

                      case DTK_MONTH:
                          tm->tm_mon += val;
!                         if (fval != 0)
!                         {
!                             int            day;
!
!                             fval *= DAYS_PER_MONTH;
!                             day = fval;
!                             tm->tm_mday += day;
!                             fval -= day;
!                             if (fval != 0)
!                             {
!                                 int            sec;
!
!                                 fval *= SECS_PER_DAY;
!                                 sec = fval;
!                                 tm->tm_sec += sec;
! #ifdef HAVE_INT64_TIMESTAMP
!                                 *fsec += rint((fval - sec) * 1000000);
! #else
!                                 *fsec += fval - sec;
! #endif
!                             }
!                         }
                          tmask = DTK_M(MONTH);
                          break;

--- 3061,3092 ----

                      case DTK_MINUTE:
                          tm->tm_min += val;
!                         adjust_fractional_seconds(fval,tm,fsec, SECS_PER_MINUTE);
                          tmask = DTK_M(MINUTE);
                          break;

                      case DTK_HOUR:
                          tm->tm_hour += val;
!                         adjust_fractional_seconds(fval,tm,fsec, SECS_PER_HOUR);
                          tmask = DTK_M(HOUR);
                          type = DTK_DAY;
                          break;

                      case DTK_DAY:
                          tm->tm_mday += val;
!                         adjust_fractional_seconds(fval,tm,fsec, SECS_PER_DAY);
                          tmask = (fmask & DTK_M(DAY)) ? 0 : DTK_M(DAY);
                          break;

                      case DTK_WEEK:
                          tm->tm_mday += val * 7;
!                         adjust_fractional_days(fval,tm,fsec,7);
                          tmask = (fmask & DTK_M(DAY)) ? 0 : DTK_M(DAY);
                          break;

                      case DTK_MONTH:
                          tm->tm_mon += val;
!                         adjust_fractional_days(fval,tm,fsec,DAYS_PER_MONTH);
                          tmask = DTK_M(MONTH);
                          break;

***************
*** 3283,3301 **** DecodeInterval(char **field, int *ftype, int nf, int range,
          fmask |= tmask;
      }

-     if (*fsec != 0)
-     {
-         int            sec;
-
- #ifdef HAVE_INT64_TIMESTAMP
-         sec = *fsec / USECS_PER_SEC;
-         *fsec -= sec * USECS_PER_SEC;
- #else
-         TMODULO(*fsec, sec, 1.0);
- #endif
-         tm->tm_sec += sec;
-     }
-
      if (is_before)
      {
          *fsec = -(*fsec);
--- 3160,3165 ----
***************
*** 3810,3815 **** AppendFsec(char * cp,fsec_t fsec)
--- 3674,3710 ----
      TrimTrailingZeros(cp);
  }

+ static char *
+ AddVerboseIntervalPart(char * cp, int value, char *units,
+                                       bool * is_zero, bool *is_before)
+ {
+     if (value==0) return cp;
+     cp += sprintf(cp," ");
+     if (*is_zero)
+         *is_before = (value < 0);
+     *is_zero = FALSE;
+     if ( (value < 0) ^ (*is_before) )
+         cp += sprintf(cp,"-");
+     value = abs(value);
+     cp += sprintf(cp,"%d %s%s",value,units,(value == 1) ? "" : "s");
+     return cp;
+ }
+
+ static char *
+ AddPostgresIntervalPart(char * cp, int value, char *units,
+                             bool * is_zero, bool *is_before)
+ {
+     if (value==0) return cp;
+     cp += sprintf(cp, "%s%s%d %s%s",
+                   (!*is_zero) ? " " : "",
+                   (*is_before && value > 0) ? "+" : "",
+                   value,
+                   units,
+                   (value != 1) ? "s" : "");
+     *is_before = (value < 0);
+     *is_zero = FALSE;
+     return cp;
+ }

  /* EncodeInterval()
   * Interpret time structure as a delta time and convert to string.
***************
*** 3834,3840 **** int
  EncodeInterval(struct pg_tm * tm, fsec_t fsec, int style, char *str)
  {
      bool        is_before = FALSE;
!     bool        is_nonzero = FALSE;
      char       *cp = str;

      /*
--- 3729,3735 ----
  EncodeInterval(struct pg_tm * tm, fsec_t fsec, int style, char *str)
  {
      bool        is_before = FALSE;
!     bool        is_zero = TRUE;
      char       *cp = str;

      /*
***************
*** 3848,3858 **** EncodeInterval(struct pg_tm * tm, fsec_t fsec, int style, char *str)
      int min   = tm->tm_min;
      int sec   = tm->tm_sec;

-     /*
-      * The sign of year and month are guaranteed to match, since they are
-      * stored internally as "month". But we'll need to check for is_before and
-      * is_nonzero when determining the signs of hour/minute/seconds fields.
-      */
      switch (style)
      {
          /* SQL Standard interval literals */
--- 3743,3748 ----
***************
*** 3955,4167 **** EncodeInterval(struct pg_tm * tm, fsec_t fsec, int style, char *str)

          /* compatible with postgresql 8.3 when DateStyle = 'iso' */
          case INTSTYLE_POSTGRES:
!             if (tm->tm_year != 0)
!             {
!                 sprintf(cp, "%d year%s",
!                         tm->tm_year, (tm->tm_year != 1) ? "s" : "");
!                 cp += strlen(cp);
!                 is_before = (tm->tm_year < 0);
!                 is_nonzero = TRUE;
!             }
!
!             if (tm->tm_mon != 0)
!             {
!                 sprintf(cp, "%s%s%d mon%s", is_nonzero ? " " : "",
!                         (is_before && tm->tm_mon > 0) ? "+" : "",
!                         tm->tm_mon, (tm->tm_mon != 1) ? "s" : "");
!                 cp += strlen(cp);
!                 is_before = (tm->tm_mon < 0);
!                 is_nonzero = TRUE;
!             }
!
!             if (tm->tm_mday != 0)
!             {
!                 sprintf(cp, "%s%s%d day%s", is_nonzero ? " " : "",
!                         (is_before && tm->tm_mday > 0) ? "+" : "",
!                         tm->tm_mday, (tm->tm_mday != 1) ? "s" : "");
!                 cp += strlen(cp);
!                 is_before = (tm->tm_mday < 0);
!                 is_nonzero = TRUE;
!             }
!
!             if (!is_nonzero || tm->tm_hour != 0 || tm->tm_min != 0 ||
!                 tm->tm_sec != 0 || fsec != 0)
              {
!                 int            minus = (tm->tm_hour < 0 || tm->tm_min < 0 ||
!                                      tm->tm_sec < 0 || fsec < 0);
!
!                 sprintf(cp, "%s%s%02d:%02d", is_nonzero ? " " : "",
                          (minus ? "-" : (is_before ? "+" : "")),
!                         abs(tm->tm_hour), abs(tm->tm_min));
!                 cp += strlen(cp);
!                 /* Mark as "non-zero" since the fields are now filled in */
!                 is_nonzero = TRUE;
!
!                 /* need fractional seconds? */
!                 if (fsec != 0)
!                 {
! #ifdef HAVE_INT64_TIMESTAMP
!                     sprintf(cp, ":%02d", abs(tm->tm_sec));
!                     cp += strlen(cp);
!                     sprintf(cp, ".%06d", Abs(fsec));
! #else
!                     fsec += tm->tm_sec;
!                     sprintf(cp, ":%012.9f", fabs(fsec));
! #endif
!                     TrimTrailingZeros(cp);
!                     cp += strlen(cp);
!                 }
!                 else
!                 {
!                     sprintf(cp, ":%02d", abs(tm->tm_sec));
!                     cp += strlen(cp);
!                 }
!             }
!             if (!is_nonzero)
!             {
!                 strcat(cp, "0");
!                 cp += strlen(cp);
              }
              break;

          /* compatible with postgresql 8.3 when DateStyle = 'sql' */
          case INTSTYLE_POSTGRES_VERBOSE:
          default:
!             strcpy(cp, "@ ");
!             cp += strlen(cp);
!
!             if (tm->tm_year != 0)
!             {
!                 int            year = tm->tm_year;
!
!                 if (tm->tm_year < 0)
!                     year = -year;
!
!                 sprintf(cp, "%d year%s", year,
!                         (year != 1) ? "s" : "");
!                 cp += strlen(cp);
!                 is_before = (tm->tm_year < 0);
!                 is_nonzero = TRUE;
!             }
!
!             if (tm->tm_mon != 0)
!             {
!                 int            mon = tm->tm_mon;
!
!                 if (is_before || (!is_nonzero && tm->tm_mon < 0))
!                     mon = -mon;
!
!                 sprintf(cp, "%s%d mon%s", is_nonzero ? " " : "", mon,
!                         (mon != 1) ? "s" : "");
!                 cp += strlen(cp);
!                 if (!is_nonzero)
!                     is_before = (tm->tm_mon < 0);
!                 is_nonzero = TRUE;
!             }
!
!             if (tm->tm_mday != 0)
!             {
!                 int            day = tm->tm_mday;
!
!                 if (is_before || (!is_nonzero && tm->tm_mday < 0))
!                     day = -day;
!
!                 sprintf(cp, "%s%d day%s", is_nonzero ? " " : "", day,
!                         (day != 1) ? "s" : "");
!                 cp += strlen(cp);
!                 if (!is_nonzero)
!                     is_before = (tm->tm_mday < 0);
!                 is_nonzero = TRUE;
!             }
!             if (tm->tm_hour != 0)
!             {
!                 int            hour = tm->tm_hour;
!
!                 if (is_before || (!is_nonzero && tm->tm_hour < 0))
!                     hour = -hour;
!
!                 sprintf(cp, "%s%d hour%s", is_nonzero ? " " : "", hour,
!                         (hour != 1) ? "s" : "");
!                 cp += strlen(cp);
!                 if (!is_nonzero)
!                     is_before = (tm->tm_hour < 0);
!                 is_nonzero = TRUE;
!             }
!
!             if (tm->tm_min != 0)
!             {
!                 int            min = tm->tm_min;
!
!                 if (is_before || (!is_nonzero && tm->tm_min < 0))
!                     min = -min;
!
!                 sprintf(cp, "%s%d min%s", is_nonzero ? " " : "", min,
!                         (min != 1) ? "s" : "");
!                 cp += strlen(cp);
!                 if (!is_nonzero)
!                     is_before = (tm->tm_min < 0);
!                 is_nonzero = TRUE;
!             }
!
!             /* fractional seconds? */
!             if (fsec != 0)
!             {
!                 fsec_t        sec;
!
! #ifdef HAVE_INT64_TIMESTAMP
!                 sec = fsec;
!                 if (is_before || (!is_nonzero && tm->tm_sec < 0))
!                 {
!                     tm->tm_sec = -tm->tm_sec;
!                     sec = -sec;
!                     is_before = TRUE;
!                 }
!                 else if (!is_nonzero && tm->tm_sec == 0 && fsec < 0)
!                 {
!                     sec = -sec;
!                     is_before = TRUE;
!                 }
!                 sprintf(cp, "%s%d.%02d secs", is_nonzero ? " " : "",
!                         tm->tm_sec, abs((int) rint(sec / 10000.0)));
!                 cp += strlen(cp);
! #else
!                 fsec += tm->tm_sec;
!                 sec = fsec;
!                 if (is_before || (!is_nonzero && fsec < 0))
!                     sec = -sec;
!
!                 sprintf(cp, "%s%.2f secs", is_nonzero ? " " : "", sec);
!                 cp += strlen(cp);
!                 if (!is_nonzero)
!                     is_before = (fsec < 0);
! #endif
!                 is_nonzero = TRUE;
!             }
!             /* otherwise, integer seconds only? */
!             else if (tm->tm_sec != 0)
!             {
!                 int            sec = tm->tm_sec;
!
!                 if (is_before || (!is_nonzero && tm->tm_sec < 0))
!                     sec = -sec;
!
!                 sprintf(cp, "%s%d sec%s", is_nonzero ? " " : "", sec,
!                         (sec != 1) ? "s" : "");
!                 cp += strlen(cp);
!                 if (!is_nonzero)
!                     is_before = (tm->tm_sec < 0);
!                 is_nonzero = TRUE;
!             }
!             if (!is_nonzero)
              {
!                 strcat(cp, "0");
                  cp += strlen(cp);
              }
              if (is_before)
-             {
                  strcat(cp, " ago");
-                 cp += strlen(cp);
-             }
              break;
      }

--- 3845,3891 ----

          /* compatible with postgresql 8.3 when DateStyle = 'iso' */
          case INTSTYLE_POSTGRES:
!             cp = AddPostgresIntervalPart(cp,year,"year",&is_zero,&is_before);
!             cp = AddPostgresIntervalPart(cp,mon ,"mon" ,&is_zero,&is_before);
!             cp = AddPostgresIntervalPart(cp,mday,"day" ,&is_zero,&is_before);
!             if (is_zero || hour != 0 || min != 0 || sec != 0 || fsec != 0)
              {
!                 int    minus = (hour < 0 || min < 0 ||
!                              sec < 0 || fsec < 0);
!                 cp += sprintf(cp, "%s%s%02d:%02d:%02d", is_zero ? "" : " ",
                          (minus ? "-" : (is_before ? "+" : "")),
!                       abs(hour), abs(min), abs(sec));
!                 AppendFsec(cp,fsec);
              }
              break;

          /* compatible with postgresql 8.3 when DateStyle = 'sql' */
          case INTSTYLE_POSTGRES_VERBOSE:
          default:
!             strcpy(cp, "@");
!             cp++;
!             cp = AddVerboseIntervalPart(cp,year,"year",&is_zero,&is_before);
!             cp = AddVerboseIntervalPart(cp,mon ,"mon" ,&is_zero,&is_before);
!             cp = AddVerboseIntervalPart(cp,mday,"day" ,&is_zero,&is_before);
!             cp = AddVerboseIntervalPart(cp,hour,"hour",&is_zero,&is_before);
!             cp = AddVerboseIntervalPart(cp,min ,"min" ,&is_zero,&is_before);
!             if (sec != 0 || fsec != 0)
              {
!                 cp += sprintf(cp, " ");
!                 if (is_zero)
!                     is_before = (sec < 0);
!                 if (is_before ^ (sec < 0 || fsec < 0))
!                     cp += sprintf(cp, "-");
!                 cp += sprintf(cp, "%d", abs(sec));
!                 AppendFsec(cp,fsec);
                  cp += strlen(cp);
+                 cp += sprintf(cp, " sec%s", (abs(sec) != 1 || fsec != 0) ? "s" : "");
+                 is_zero = FALSE;
              }
+             if (is_zero)
+                 strcat(cp, " 0");
              if (is_before)
                  strcat(cp, " ago");
              break;
      }

*** a/src/backend/utils/adt/nabstime.c
--- b/src/backend/utils/adt/nabstime.c
***************
*** 634,639 **** reltimein(PG_FUNCTION_ARGS)
--- 634,642 ----
      if (dterr == 0)
          dterr = DecodeInterval(field, ftype, nf, INTERVAL_FULL_RANGE,
                                 &dtype, tm, &fsec);
+     if (dterr == DTERR_BAD_FORMAT)
+         dterr = DecodeISO8601Interval(str, tm, &fsec);
+
      if (dterr != 0)
      {
          if (dterr == DTERR_FIELD_OVERFLOW)
***************
*** 646,651 **** reltimein(PG_FUNCTION_ARGS)
--- 649,664 ----
          case DTK_DELTA:
              result = ((tm->tm_hour * MINS_PER_HOUR + tm->tm_min) * SECS_PER_MINUTE) + tm->tm_sec;
              result += tm->tm_year * SECS_PER_YEAR + ((tm->tm_mon * DAYS_PER_MONTH) + tm->tm_mday) * SECS_PER_DAY;
+             if (fsec != 0)
+             {
+                 int            extra_seconds;
+ #ifdef HAVE_INT64_TIMESTAMP
+                 extra_seconds = (fsec / INT64CONST(1000000));
+ #else
+                 TMODULO(fsec, extra_seconds, 1e0);
+ #endif
+                 result += extra_seconds;
+             }
              break;

          default: