Re: Re: Updated interval patches (SQL std output, ISO8601 intervals, and interval rounding) - Mailing list pgsql-hackers

Ron Mayer wrote:
> Ron Mayer wrote:
>  > Tom Lane wrote:
>  >> In fact, given that we are now
>  >> somewhat SQL-compliant on interval input, a GUC that selected
>  >> PG traditional, SQL-standard, or ISO 8601 interval output format seems
>  >> like it could be a good idea.
>
> Attached are updated versions of the Interval patches ...


# Patch 2:
    ISO 8601 Formatted Interval Input and Output

    This patch adds another IntervalStyle 'iso_8601' to output ISO 8601
    Time Intervals of the "format with designators". These are a bit
    more flexible than Sql Standard intervals in that (like postgres)
    they can express both years and days in the same interval value.

    Reason for the patch:SQL Standard Intervals are limited compared to
    postgres in what they allow (no mixed year-month and day-time
    components). ISO8601 intervals allow such intervals and are easier
    for machines to parse than the traditional postgres formats.

    This patch depends on the IntervalStyle patch mentioned above.
*** a/doc/src/sgml/datatype.sgml
--- b/doc/src/sgml/datatype.sgml
***************
*** 1975,1980 **** January 8 04:05:06 1999 PST
--- 1975,1996 ----
       </para>

       <para>
+       Alternatively, <type>interval</type> values can be written as
+       ISO 8601 time intervals, using the "Format with time-unit designators",
+       or PnYnMnDTnHnMnS.   This format always starts with the character
+       <literal>'P'</>, followed by a string of values followed by single
+       character time-unit designators.  A <literal>'T'</> separates the
+       date and time parts of the interval.
+       In this format, <literal>'n'</> gets replaced by a number, and
+        <literal>Y</> represents years,
+        <literal>M</> (in the date part) months,
+        <literal>D</> months,
+        <literal>H</> hours,
+        <literal>M</> (in the time part) minutes,
+        and <literal>S</> seconds.
+      </para>
+
+      <para>
        Internally <type>interval</> values are stored as months, days,
        and seconds. This is done because the number of days in a month
        varies, and a day can have 23 or 25 hours if a daylight savings
***************
*** 2224,2230 **** January 8 04:05:06 1999 PST

      <para>
       The output format of the interval types can be set to one of the
!      three styles <literal>sql_standard</>,
       <literal>postgres</>, or <literal>postgres_verbose</>.
       The default is the <literal>postgres</> format.
       <xref
--- 2240,2246 ----

      <para>
       The output format of the interval types can be set to one of the
!      four styles <literal>sql_standard</>, <literal>iso_8601</>,
       <literal>postgres</>, or <literal>postgres_verbose</>.
       The default is the <literal>postgres</> format.
       <xref
***************
*** 2244,2249 **** January 8 04:05:06 1999 PST
--- 2260,2281 ----
      </para>

      <para>
+      The <literal>iso_8601</> style will output ISO 8601
+      time intervals using the "format with time-unit designators"
+      This format always starts with the character
+       <literal>'P'</>, followed by a string of values followed by single
+       character time-unit designators.  A <literal>'T'</> separates the
+       date and time parts of the interval.
+       In this format, <literal>'n'</> gets replaced by a number, and
+        <literal>Y</> represents years,
+        <literal>M</> (in the date part) months,
+        <literal>D</> months,
+        <literal>H</> hours,
+        <literal>M</> (in the time part) minutes,
+        and <literal>S</> seconds.
+     </para>
+
+     <para>
       The <literal>postgres</> style will output intervals that match
       the style PostgreSQL 8.3 outputed when the <xref linkend="guc-datestyle">
       parameter was set to <literal>ISO</>.
***************
*** 2274,2279 **** January 8 04:05:06 1999 PST
--- 2306,2317 ----
            <entry>-1-2 +3 -4:05:06</entry>
           </row>
           <row>
+           <entry>iso_8601</entry>
+           <entry>P1Y2M</entry>
+           <entry>P3DT4H5M6</entry>
+           <entry>P-1Y-2M3DT-4H-5M-6</entry>
+          </row>
+          <row>
            <entry>postgres</entry>
            <entry>1 year 2 mons</entry>
            <entry>3 days 04:05:06</entry>
*** a/src/backend/commands/variable.c
--- b/src/backend/commands/variable.c
***************
*** 248,253 **** assign_intervalstyle(const char *value, bool doit, GucSource source)
--- 248,257 ----
      {
          newIntervalStyle = INTSTYLE_SQL_STANDARD;
      }
+     else if (pg_strcasecmp(value, "iso_8601") == 0)
+     {
+         newIntervalStyle = INTSTYLE_ISO_8601;
+     }
      else
      {
          ereport(GUC_complaint_elevel(source),
*** a/src/backend/utils/adt/datetime.c
--- b/src/backend/utils/adt/datetime.c
***************
*** 2723,2728 **** DecodeSpecial(int field, char *lowtoken, int *val)
--- 2723,2865 ----
  }


+ /*
+  * Small helper functions to avoid cut&paste code in DecodeIso8601Interval
+  */
+ static void
+ adjust_fractional_seconds(double fval,struct pg_tm * tm, fsec_t *fsec, int scale)
+ {
+     int    sec;
+     if (fval == 0) return;
+     fval       *= scale;
+     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()
+  *  Decode an ISO 8601 "Representation of time-interval by
+  *  duration only.... basic extended format" from ISO 8601 section 5.5.4.2
+  *
+  *  Examples:  P1D  for 1 day
+  *             PT1H for 1 hour
+  *             P2Y6M7DT1H30M for 2 years, 6 months, 7 days 1 hour 30 min
+  *
+  *  Returns DTERR_BAD_FORMAT if the field is not of this type.
+  *
+  *  A couple exceptions from the spec:
+  *   - a week field ('W') may coexist with other units
+  *   - allows decimals in fields other than the least significant unit.
+  */
+ int
+ DecodeISO8601Interval(char *str, struct pg_tm * tm, fsec_t *fsec)
+ {
+     char    unit;
+     int        val;
+     double    fval;
+     int        datepart = true;
+     int        negative = false;
+
+     tm->tm_year = 0;
+     tm->tm_mon = 0;
+     tm->tm_mday = 0;
+     tm->tm_hour = 0;
+     tm->tm_min = 0;
+     tm->tm_sec = 0;
+     *fsec = 0;
+
+     if (strlen(str)<3 || !(str[0] == 'P'))
+     {
+         return DTERR_BAD_FORMAT;
+     }
+     str++;
+
+     while (*str)
+     {
+         if (*str == 'T') /* T indicates the beginning of the time part */
+         {
+             datepart = false;
+             str++;
+             continue;
+         }
+         if (! (isdigit(*str) || *str == '-' || *str=='.') )
+             return DTERR_BAD_FORMAT;
+
+         negative = (*str == '-');
+         fval     = strtod(str, &str);
+         val         = fval;
+         fval     = fval - val;
+         unit     = *str;
+         str++;
+
+         if (datepart)
+         {
+             switch (unit) /* Y M D W */
+             {
+                 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 */
+             }
+         }
+         else
+         {
+             switch (unit) /* H M S */
+             {
+                 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;
+ }
+
+
+
  /* DecodeInterval()
   * Interpret previously parsed fields for general time interval.
   * Returns 0 if successful, DTERR code if bogus input detected.
***************
*** 3621,3648 **** EncodeDateTime(struct pg_tm * tm, fsec_t fsec, int *tzp, char **tzn, int style,
      return TRUE;
  }

! /*
!  * Small helper funciton to avoid copy&paste in EncodeInterval below
   */
  static void
! AppendSeconds(char * cp,int sec, fsec_t fsec)
  {
      if (fsec==0)
      {
!         sprintf(cp, "%02d", abs(sec));
      }
      else
      {
  #ifdef HAVE_INT64_TIMESTAMP
!         sprintf(cp, "%02d.%06d", sec, Abs(fsec));
  #else
!         sprintf(cp, "%012.9f", fabs(sec + fsec));
  #endif
          TrimTrailingZeros(cp);
      }
  }


  /* EncodeInterval()
   * Interpret time structure as a delta time and convert to string.
   *
--- 3758,3795 ----
      return TRUE;
  }

!
! /*
!  * Small helper functions to avoid cut&paste in EncodeInterval below
   */
+
+ static char *
+ AddISO8601IntervalPart(char * cp, int value, char units)
+ {
+     if (value==0) return cp;
+     return cp + sprintf(cp,"%d%c",value,units);
+ }
+
  static void
! AppendSeconds(char * cp,int sec, fsec_t fsec, bool fillzeros)
  {
      if (fsec==0)
      {
!         sprintf(cp, fillzeros ? "%02d" : "%d" , abs(sec));
      }
      else
      {
  #ifdef HAVE_INT64_TIMESTAMP
!         sprintf(cp, fillzeros ? "%02d.%06d" : "%d.%06d", abs(sec), abs(fsec));
  #else
!         sprintf(cp, fillzeros ? "%09.6f" : "%.6f", fabs(sec + fsec));
  #endif
          TrimTrailingZeros(cp);
      }
  }


+
  /* EncodeInterval()
   * Interpret time structure as a delta time and convert to string.
   *
***************
*** 3734,3740 **** EncodeInterval(struct pg_tm * tm, fsec_t fsec, int style, char *str)
                          year_sign,abs(year),abs(mon),
                          day_sign,abs(mday),
                          sec_sign,abs(hour),abs(min));
!                 AppendSeconds(cp,sec,fsec);
              }
              else if (has_year_month)
              {
--- 3881,3887 ----
                          year_sign,abs(year),abs(mon),
                          day_sign,abs(mday),
                          sec_sign,abs(hour),abs(min));
!                 AppendSeconds(cp,sec,fsec,true);
              }
              else if (has_year_month)
              {
***************
*** 3743,3758 **** EncodeInterval(struct pg_tm * tm, fsec_t fsec, int style, char *str)
              else if (has_day)
              {
                  cp += sprintf(cp,"%d %d:%02d:",mday,hour,min);
!                 AppendSeconds(cp,sec,fsec);
              }
              else
              {
                  cp += sprintf(cp,"%d:%02d:",hour,min);
!                 AppendSeconds(cp,sec,fsec);
              }
              break;
          }

          /* compatible with postgresql 8.3 when DateStyle = 'iso' */
          case INTSTYLE_POSTGRES:
              if (tm->tm_year != 0)
--- 3890,3932 ----
              else if (has_day)
              {
                  cp += sprintf(cp,"%d %d:%02d:",mday,hour,min);
!                 AppendSeconds(cp,sec,fsec,true);
              }
              else
              {
                  cp += sprintf(cp,"%d:%02d:",hour,min);
!                 AppendSeconds(cp,sec,fsec,true);
              }
              break;
          }

+         /* ISO 8601 "time-intervals by duration only */
+         case INTSTYLE_ISO_8601:
+             if ((year == 0) && (mon == 0) && (mday == 0) &&
+                 (hour == 0) && (min == 0) && (sec  == 0) && (fsec == 0))
+             {
+                 sprintf(cp,"PT0S");
+                 break;
+             }
+             cp += sprintf(cp,"P");
+             cp = AddISO8601IntervalPart(cp,year,'Y');
+             cp = AddISO8601IntervalPart(cp,mon ,'M');
+             cp = AddISO8601IntervalPart(cp,mday,'D');
+             if ((hour != 0) || (min != 0) || (sec != 0) || (fsec != 0))
+             {
+                 cp += sprintf(cp,"T");
+             }
+             cp = AddISO8601IntervalPart(cp,hour,'H');
+             cp = AddISO8601IntervalPart(cp,min ,'M');
+             if ((sec != 0) || (fsec != 0))
+             {
+                 cp += sprintf(cp, "%s", (sec<0 || fsec<0) ? "-" : "");
+                 AppendSeconds(cp,sec,fsec,false);
+                 cp += strlen(cp);
+                 cp += sprintf(cp,"S");
+             }
+             break;
+
          /* compatible with postgresql 8.3 when DateStyle = 'iso' */
          case INTSTYLE_POSTGRES:
              if (tm->tm_year != 0)
*** a/src/backend/utils/adt/timestamp.c
--- b/src/backend/utils/adt/timestamp.c
***************
*** 627,632 **** interval_in(PG_FUNCTION_ARGS)
--- 627,635 ----
                            ftype, MAXDATEFIELDS, &nf);
      if (dterr == 0)
          dterr = DecodeInterval(field, ftype, nf, range, &dtype, tm, &fsec);
+     if (dterr == DTERR_BAD_FORMAT)
+         dterr = DecodeISO8601Interval(str, tm, &fsec);
+
      if (dterr != 0)
      {
          if (dterr == DTERR_FIELD_OVERFLOW)
*** a/src/bin/psql/tab-complete.c
--- b/src/bin/psql/tab-complete.c
***************
*** 1956,1963 **** psql_completion(char *text, int start, int end)
          else if (pg_strcasecmp(prev2_wd, "IntervalStyle") == 0)
          {
              static const char *const my_list[] =
!             {"postgres","postgres_verbose", "sql_standard", NULL};
!
              COMPLETE_WITH_LIST(my_list);
          }
          else if (pg_strcasecmp(prev2_wd, "GEQO") == 0)
--- 1956,1962 ----
          else if (pg_strcasecmp(prev2_wd, "IntervalStyle") == 0)
          {
              static const char *const my_list[] =
!             {"postgres","postgres_verbose", "sql_standard", "iso_8601", NULL};
              COMPLETE_WITH_LIST(my_list);
          }
          else if (pg_strcasecmp(prev2_wd, "GEQO") == 0)
*** a/src/include/miscadmin.h
--- b/src/include/miscadmin.h
***************
*** 197,206 **** extern int    DateOrder;
--- 197,208 ----
   *   INTSTYLE_POSTGRES             Like Postgres8.3 when DateStyle = 'iso'
   *   INTSTYLE_POSTGRES_VERBOSE     Like Postgres8.3 when DateStyle = 'sql'
   *   INTSTYLE_SQL_STANDARD         SQL standard interval literals
+  *   INTSTYLE_ISO_8601             Specifies ISO-8601-basic formatted intervals
   */
  #define INTSTYLE_POSTGRES             0
  #define INTSTYLE_POSTGRES_VERBOSE     1
  #define INTSTYLE_SQL_STANDARD         2
+ #define INTSTYLE_ISO_8601             3

  extern int    IntervalStyle;

*** a/src/include/utils/datetime.h
--- b/src/include/utils/datetime.h
***************
*** 292,297 **** extern int DecodeTimeOnly(char **field, int *ftype,
--- 292,300 ----
  extern int DecodeInterval(char **field, int *ftype,
                 int nf, int range, int *dtype,
                 struct pg_tm * tm, fsec_t *fsec);
+ extern int DecodeISO8601Interval(char *str,
+                struct pg_tm * tm, fsec_t *fsec);
+
  extern void DateTimeParseError(int dterr, const char *str,
                     const char *datatype);

*** a/src/test/regress/expected/interval.out
--- b/src/test/regress/expected/interval.out
***************
*** 632,634 **** SELECT  interval '1 years 2 months -3 days 4 hours 5 minutes 6.789 seconds',
--- 632,663 ----
   +1-2 -3 +4:05:06.789 | -1-2 +3 -4:05:06.789
  (1 row)

+ -- test outputting iso8601 intervals
+ SET IntervalStyle to iso_8601;
+ select  interval '0'                                AS "zero",
+         interval '1-2'                              AS "a year 2 months",
+         interval '1 2:03:04'                        AS "a bit over a day",
+         interval '2:03:04.45679'                    AS "a bit over 2 hours",
+         (interval '1-2' + interval '3 4:05:06.7')   AS "all fields",
+         (interval '1-2' - interval '3 4:05:06.7')   AS "mixed sign",
+         (- interval '1-2' + interval '3 4:05:06.7') AS "negative"
+         ;
+  zero | a year 2 months | a bit over a day | a bit over 2 hours |    all fields     |      mixed sign       |
negative       
+
------+-----------------+------------------+--------------------+-------------------+-----------------------+---------------------
+  PT0S | P1Y2M           | P1DT2H3M4S       | PT2H3M4.45679S     | P1Y2M3DT4H5M6.70S | P1Y2M-3DT-4H-5M-6.70S |
P-1Y-2M3DT4H5M6.70S
+ (1 row)
+
+ -- test inputting iso8601 intervals
+ SET IntervalStyle to sql_standard;
+ select  interval 'P0Y'                    AS "zero",
+         interval 'P1Y2M'                  AS "a year 2 months",
+         interval 'P1W'                    AS "a week",
+         interval 'P1DT2H3M4S'             AS "a bit over a day",
+         interval 'P1Y2M3DT4H5M6.7S'       AS "all fields",
+         interval 'P-1Y-2M-3DT-4H-5M-6.7S' AS "negative",
+         interval 'PT-0.1S'                AS "fractional second";
+  zero | a year 2 months |  a week   | a bit over a day |     all fields      |      negative       | fractional
second 
+
------+-----------------+-----------+------------------+---------------------+---------------------+-------------------
+  0    | 1-2             | 7 0:00:00 | 1 2:03:04        | +1-2 +3 +4:05:06.70 | -1-2 -3 -4:05:06.70 | -0:00:00.10
+ (1 row)
+
*** a/src/test/regress/sql/interval.sql
--- b/src/test/regress/sql/interval.sql
***************
*** 190,192 **** SELECT  interval '0'                       AS "zero",
--- 190,213 ----
  -- test a couple non-standard interval values too
  SELECT  interval '1 years 2 months -3 days 4 hours 5 minutes 6.789 seconds',
          - interval '1 years 2 months -3 days 4 hours 5 minutes 6.789 seconds';
+
+ -- test outputting iso8601 intervals
+ SET IntervalStyle to iso_8601;
+ select  interval '0'                                AS "zero",
+         interval '1-2'                              AS "a year 2 months",
+         interval '1 2:03:04'                        AS "a bit over a day",
+         interval '2:03:04.45679'                    AS "a bit over 2 hours",
+         (interval '1-2' + interval '3 4:05:06.7')   AS "all fields",
+         (interval '1-2' - interval '3 4:05:06.7')   AS "mixed sign",
+         (- interval '1-2' + interval '3 4:05:06.7') AS "negative"
+         ;
+
+ -- test inputting iso8601 intervals
+ SET IntervalStyle to sql_standard;
+ select  interval 'P0Y'                    AS "zero",
+         interval 'P1Y2M'                  AS "a year 2 months",
+         interval 'P1W'                    AS "a week",
+         interval 'P1DT2H3M4S'             AS "a bit over a day",
+         interval 'P1Y2M3DT4H5M6.7S'       AS "all fields",
+         interval 'P-1Y-2M-3DT-4H-5M-6.7S' AS "negative",
+         interval 'PT-0.1S'                AS "fractional second";