Re: Patch for SQL-Standard Interval output and decoupling DateStyle from IntervalStyle - Mailing list pgsql-hackers

Brendan Jurd wrote:
> ...Sep 18, 2008...Ron Mayer <rm_pg@cheapcomplexdevices.com> wrote:
>>     (1) ...GUC called "IntervalStyle"...
>>     (2) ...interval style that will match the SQL standards...
>
> ...an initial review...
>
> When I ran the regression tests, I got one failure in the new interval

Fixed, and I did a bit more testing both with and without HAVE_INT64_TIMESTAMP.

> The C code has some small stylistic inconsistencies; ...
> ... spaces around binary operators are missing (e.g., "(fsec<0)").

Thanks.  Fixed these.

> ...function calls missing the space after the argument separator...

I think I fixed all these now too.

> The new documentation is good in terms of content, but there are some
> minor stylistic and spelling cleanups I would suggest.
> ...variously..."SQL standard", "SQL-standard" and "SQL Standard"...

Got it.  There are a few inconsistencies elsewhere in the file
talking about other data types.  I wonder if I should fix those
as well.

> These sentences in datatype.sgml are a bit awkward ...
> I would go with something more along the lines of...

Yes.  Thanks for the better wording.

> I don't think "old releases" is specific enough.

Yup - fixed that too.

> That's all the feedback I have for the moment.  I hope you found my
> comments helpful.  I'll be setting the status of this patch to
> "Returned with Feedback" and wait for your responses before I move
> forward with reviewing the other patches.

Great.   I've tried to update the style on my remaining patches as well.


In addition, I've added to the docs describing how I use
explicit '+' and '-' signs to disambiguate the mixed-sign
non-standard intervals when in the sql_standard mode.


As before the 3 patches are at: http://0ape.com/postgres_interval_patches/
and http://git.forensiclogic.com/postgresql/ and
http://git.forensiclogic.com/?p=postgresql;a=shortlog;h=refs/heads/cleanup

I'm attaching the patch dealing with sql standard intervals here for the archives.

*** a/doc/src/sgml/config.sgml
--- b/doc/src/sgml/config.sgml
***************
*** 4013,4018 **** SET XML OPTION { DOCUMENT | CONTENT };
--- 4013,4056 ----
        </listitem>
       </varlistentry>

+      <varlistentry id="guc-intervalstyle" xreflabel="IntervalStyle">
+       <term><varname>IntervalStyle</varname> (<type>string</type>)</term>
+       <indexterm>
+        <primary><varname>IntervalStyle</> configuration parameter</primary>
+       </indexterm>
+       <listitem>
+        <para>
+         Sets the display format for interval values.
+         The value <literal>sql_standard</> will produce
+         output matching <acronym>SQL</acronym> standard
+         interval literals for values that conform to the
+         <acronym>SQL</acronym> standard (either year-month
+         only or date-time only; and no mixing of positive
+         and negative components).
+
+         The value <literal>postgres</> will produce output
+         matching PostgreSQL releases prior to 8.4
+         when the <xref linkend="guc-datestyle">
+         parameter was set to <literal>ISO</>.
+
+         The value <literal>postgres_verbose</> will produce output
+         matching PostgreSQL releases prior to 8.4
+         when the <xref linkend="guc-datestyle">
+         parameter was set to <literal>SQL</>.
+        </para>
+        <para>
+         The IntervalStyle GUC also affects the interpretation
+         of one ambiguous interval literal input.  In SQL 2008
+         the negative sign in the interval literal '-1 2:03:04'
+         applies to both the days and hour/minute/second parts.
+         PostgreSQL traditionally only applied the negative
+         sign to the days part.  If IntervalStyle is set to
+         <literal>sql_standard</literal> it will follow the standard
+         otherwise it uses the traditional postgres interpretation.
+        </para>
+       </listitem>
+      </varlistentry>
+
       <varlistentry id="guc-timezone" xreflabel="timezone">
        <term><varname>timezone</varname> (<type>string</type>)</term>
        <indexterm>
*** a/doc/src/sgml/datatype.sgml
--- b/doc/src/sgml/datatype.sgml
***************
*** 1962,1968 **** January 8 04:05:06 1999 PST
        a combination of years and months can be specified with a dash;
        for example <literal>'200-10'</> is read the same as <literal>'200 years
        10 months'</>.  (These shorter forms are in fact the only ones allowed
!       by the SQL standard.)
       </para>

       <para>
--- 1962,1968 ----
        a combination of years and months can be specified with a dash;
        for example <literal>'200-10'</> is read the same as <literal>'200 years
        10 months'</>.  (These shorter forms are in fact the only ones allowed
!       by the <acronym>SQL</acronym> standard.)
       </para>

       <para>
***************
*** 2213,2218 **** January 8 04:05:06 1999 PST
--- 2213,2310 ----
      </para>
     </sect2>

+    <sect2 id="interval-output">
+     <title>Interval Output</title>
+
+     <indexterm>
+      <primary>interval</primary>
+      <secondary>output format</secondary>
+      <seealso>formatting</seealso>
+     </indexterm>
+
+     <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
+      linkend="interval-style-output-table"> shows examples of each
+      output style.
+     </para>
+
+     <para>
+      The <literal>sql_standard</> style will output SQL standard
+      interval literal strings where the value of the interval
+      value consists of only a year-month component or a datetime
+      component (as required by the <acronym>SQL</acronym> standard).
+      For an interval containing both a year-month and a datetime
+      component, the output will be a <acronym>SQL</acronym> standard
+      unquoted year-month literal string concatenated with a
+      <acronym>SQL</acronym> standard unquoted datetime literal
+      string with a space in between; and '+' and '-' signs added
+      to disambiguate mixed-sign intervals.
+     </para>
+
+     <para>
+      The <literal>postgres</> style will output intervals
+      matching those output by PostgreSQL releases prior to 8.4
+      when the <xref linkend="guc-datestyle">
+      parameter was set to <literal>ISO</>.
+     </para>
+
+     <para>
+      The <literal>postgres_verbose</> style will output intervals
+      matching those output by PostgreSQL releases prior to 8.4
+      when the <xref linkend="guc-datestyle">
+      parameter was set to <literal>SQL</>.
+     </para>
+
+      <table id="interval-style-output-table">
+        <title>Interval Style Example</title>
+        <tgroup cols="2">
+         <thead>
+          <row>
+           <entry>Style Specification</entry>
+           <entry>Year-Month Interval</entry>
+           <entry>DateTime Interval</entry>
+           <entry>Nonstandardrd Extended Interval</entry>
+          </row>
+         </thead>
+         <tbody>
+          <row>
+           <entry>sql_standard</entry>
+           <entry>1-2</entry>
+           <entry>3 4:05:06</entry>
+           <entry>-1-2 +3 -4:05:06</entry>
+          </row>
+          <row>
+           <entry>postgres</entry>
+           <entry>1 year 2 mons</entry>
+           <entry>3 days 04:05:06</entry>
+           <entry>-1 year -2 mons +3 days -04:05:06</entry>
+          </row>
+          <row>
+           <entry>postgres_verbose</entry>
+           <entry>@ 1 year 2 mons</entry>
+           <entry>@ 3 days 4 hours 5 mins 6 secs</entry>
+           <entry>@ 1 year 2 mons -3 days 4 hours 5 mins 6 secs ago</entry>
+          </row>
+         </tbody>
+          </tgroup>
+     </table>
+
+      <para>
+      Note that <literal>sql_standard</> style will only produce strictly
+      standards-conforming interval literals when given a strictly
+      <acronym>SQL</acronym> standard interval value - meaning that
+      it needs to be a pure year-month or datetime interval and not
+      mix positive and negative components.
+      </para>
+
+    </sect2>
+
+
+
     <sect2 id="datatype-timezones">
      <title>Time Zones</title>

*** a/src/backend/commands/variable.c
--- b/src/backend/commands/variable.c
***************
*** 229,234 **** assign_datestyle(const char *value, bool doit, GucSource source)
--- 229,271 ----


  /*
+  * assign_intervalstyle: GUC assign_hook for datestyle
+  */
+ const char *
+ assign_intervalstyle(const char *value, bool doit, GucSource source)
+ {
+     int    newIntervalStyle = IntervalStyle;
+     char *    result = (char *) malloc(32);
+     if (pg_strcasecmp(value, "postgres") == 0)
+     {
+         newIntervalStyle = INTSTYLE_POSTGRES;
+     }
+     else if (pg_strcasecmp(value, "postgres_verbose") == 0)
+     {
+         newIntervalStyle = INTSTYLE_POSTGRES_VERBOSE;
+     }
+     else if (pg_strcasecmp(value, "sql_standard") == 0)
+     {
+         newIntervalStyle = INTSTYLE_SQL_STANDARD;
+     }
+     else
+     {
+         ereport(GUC_complaint_elevel(source),
+                 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+                  errmsg("unrecognized \"intervalstyle\" key word: \"%s\"",
+                             value)));
+         return NULL;
+     }
+     if (doit)
+     {
+         IntervalStyle = newIntervalStyle;
+         strcpy(result, value);
+     }
+     return result;
+ }
+
+
+ /*
   * TIMEZONE
   */

*** a/src/backend/utils/adt/datetime.c
--- b/src/backend/utils/adt/datetime.c
***************
*** 2766,2771 **** DecodeInterval(char **field, int *ftype, int nf, int range,
--- 2766,2787 ----
              case DTK_TIME:
                  dterr = DecodeTime(field[i], fmask, range,
                                     &tmask, tm, fsec);
+                 if (IntervalStyle == INTSTYLE_SQL_STANDARD &&
+                     field[0][0] == '-' && i == 1 &&
+                     field[i][0] != '-' && field[i][0] != '+')
+                 {
+                     /*
+                      * The SQL Standard defines the interval literal
+                      *   '-1 1:00:00'
+                      * to mean "negative 1 days and negative one hours"
+                      * while Postgres traditionally treated this as
+                      * to mean "negative 1 days and positive one hours"
+                      */
+                     tm->tm_hour = -tm->tm_hour;
+                     tm->tm_min = -tm->tm_min;
+                     tm->tm_sec = -tm->tm_sec;
+                     *fsec = - *fsec;
+                 }
                  if (dterr)
                      return dterr;
                  type = DTK_DAY;
***************
*** 3605,3610 **** EncodeDateTime(struct pg_tm * tm, fsec_t fsec, int *tzp, char **tzn, int style,
--- 3621,3647 ----
      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", abs(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.
***************
*** 3613,3618 **** EncodeDateTime(struct pg_tm * tm, fsec_t fsec, int *tzp, char **tzn, int style,
--- 3650,3666 ----
   * Actually, afaik ISO does not address time interval formatting,
   *    but this looks similar to the spec for absolute date/time.
   * - thomas 1998-04-30
+  *
+  * Actually, afaik, ISO 8601 does specify formats for "time
+  * intervals...[of the]...format with time-unit designators", which
+  * are pretty ugly.  The format looks something like
+  *     P1Y1M1DT1H1M1.12345S
+  * but useful for exchanging data with computers instead of humans.
+  * - ron 2003-07-14
+  *
+  * And ISO's SQL 2008 standard specifies standards for
+  * "year-month literal"s (that look like '2-3') and
+  * "day-time literal"s (that look like ('4 5:6:7')
   */
  int
  EncodeInterval(struct pg_tm * tm, fsec_t fsec, int style, char *str)
***************
*** 3621,3626 **** EncodeInterval(struct pg_tm * tm, fsec_t fsec, int style, char *str)
--- 3669,3681 ----
      bool        is_nonzero = FALSE;
      char       *cp = str;

+     int year  = tm->tm_year;
+     int mon   = tm->tm_mon;
+     int mday  = tm->tm_mday;
+     int hour  = tm->tm_hour;
+     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
***************
*** 3628,3635 **** EncodeInterval(struct pg_tm * tm, fsec_t fsec, int style, char *str)
       */
      switch (style)
      {
!             /* compatible with ISO date formats */
!         case USE_ISO_DATES:
              if (tm->tm_year != 0)
              {
                  sprintf(cp, "%d year%s",
--- 3683,3760 ----
       */
      switch (style)
      {
!         /* SQL Standard interval literals */
!         case INTSTYLE_SQL_STANDARD:
!         {
!             bool has_negative = (year < 0) || (mon  < 0) ||
!                                 (mday < 0) || (hour < 0) ||
!                                 (min  < 0) || (sec  < 0) || (fsec < 0);
!             bool has_positive = (year > 0) || (mon  > 0) ||
!                                 (mday > 0) || (hour > 0) ||
!                                 (min  > 0) || (sec  > 0) || (fsec > 0);
!             bool has_year_month = (year != 0) || (mon  != 0);
!             bool has_datetime   = (hour != 0) || (min  != 0) ||
!                                   (sec  != 0) || (fsec != 0) || (mday != 0);
!             bool has_day        = (mday != 0);
!             bool sql_standard_value = (!(has_negative && has_positive)) &&
!                                       (!(has_year_month && has_datetime));
!             /*
!              * SQL Standard wants only 1 "<sign>" preceeding the whole
!              * interval.
!              */
!             if (has_negative && sql_standard_value)
!             {
!                 sprintf(cp, "-");
!                 cp++;
!                 year = -year;
!                 mon  = -mon;
!                 mday = -mday;
!                 hour = -hour;
!                 min  = -min;
!                 sec  = -sec;
!                 fsec = -fsec;
!             }
!             if (!has_negative && !has_positive)
!             {
!                 sprintf(cp, "0");
!             }
!             else if (!sql_standard_value)
!             {
!                 /*
!                  * For non sql-standard interval values,
!                  * force outputting the signs to avoid
!                  * ambiguities with intervals with mixed
!                  * sign components.
!                  */
!                 char year_sign = (year < 0 || mon < 0) ? '-' : '+';
!                 char day_sign = (mday < 0) ? '-' : '+';
!                 char sec_sign = (hour < 0 || min < 0 || sec < 0 || fsec < 0)
!                                 ? '-' : '+';
!                 cp += sprintf(cp, "%c%d-%d %c%d %c%d:%02d:",
!                         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)
!             {
!                 sprintf(cp, "%d-%d", year, mon);
!             }
!             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)
              {
                  sprintf(cp, "%d year%s",
***************
*** 3692,3700 **** EncodeInterval(struct pg_tm * tm, fsec_t fsec, int style, char *str)
                      cp += strlen(cp);
                  }
              }
              break;

!         case USE_POSTGRES_DATES:
          default:
              strcpy(cp, "@ ");
              cp += strlen(cp);
--- 3817,3831 ----
                      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);
***************
*** 3821,3842 **** EncodeInterval(struct pg_tm * tm, fsec_t fsec, int style, char *str)
                      is_before = (tm->tm_sec < 0);
                  is_nonzero = TRUE;
              }
              break;
      }

-     /* identically zero? then put in a unitless zero... */
-     if (!is_nonzero)
-     {
-         strcat(cp, "0");
-         cp += strlen(cp);
-     }
-
-     if (is_before && (style != USE_ISO_DATES))
-     {
-         strcat(cp, " ago");
-         cp += strlen(cp);
-     }
-
      return 0;
  }    /* EncodeInterval() */

--- 3952,3970 ----
                      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;
      }

      return 0;
  }    /* EncodeInterval() */

*** a/src/backend/utils/adt/nabstime.c
--- b/src/backend/utils/adt/nabstime.c
***************
*** 671,677 **** reltimeout(PG_FUNCTION_ARGS)
      char        buf[MAXDATELEN + 1];

      reltime2tm(time, tm);
!     EncodeInterval(tm, 0, DateStyle, buf);

      result = pstrdup(buf);
      PG_RETURN_CSTRING(result);
--- 671,677 ----
      char        buf[MAXDATELEN + 1];

      reltime2tm(time, tm);
!     EncodeInterval(tm, 0, IntervalStyle, buf);

      result = pstrdup(buf);
      PG_RETURN_CSTRING(result);
*** a/src/backend/utils/adt/timestamp.c
--- b/src/backend/utils/adt/timestamp.c
***************
*** 677,683 **** interval_out(PG_FUNCTION_ARGS)
      if (interval2tm(*span, tm, &fsec) != 0)
          elog(ERROR, "could not convert interval to tm");

!     if (EncodeInterval(tm, fsec, DateStyle, buf) != 0)
          elog(ERROR, "could not format interval");

      result = pstrdup(buf);
--- 677,683 ----
      if (interval2tm(*span, tm, &fsec) != 0)
          elog(ERROR, "could not convert interval to tm");

!     if (EncodeInterval(tm, fsec, IntervalStyle, buf) != 0)
          elog(ERROR, "could not format interval");

      result = pstrdup(buf);
*** a/src/backend/utils/init/globals.c
--- b/src/backend/utils/init/globals.c
***************
*** 88,93 **** bool        ExitOnAnyError = false;
--- 88,94 ----

  int            DateStyle = USE_ISO_DATES;
  int            DateOrder = DATEORDER_MDY;
+ int            IntervalStyle = INTSTYLE_POSTGRES;
  bool        HasCTZSet = false;
  int            CTimeZone = 0;

*** a/src/backend/utils/misc/guc.c
--- b/src/backend/utils/misc/guc.c
***************
*** 366,371 **** static bool session_auth_is_superuser;
--- 366,372 ----
  static double phony_random_seed;
  static char *client_encoding_string;
  static char *datestyle_string;
+ static char *intervalstyle_string;
  static char *locale_collate;
  static char *locale_ctype;
  static char *server_encoding_string;
***************
*** 2078,2083 **** static struct config_string ConfigureNamesString[] =
--- 2079,2094 ----
          "ISO, MDY", assign_datestyle, NULL
      },

+      {
+         {"IntervalStyle", PGC_USERSET, CLIENT_CONN_LOCALE,
+             gettext_noop("Sets the display format for interval values."),
+             gettext_noop(""),
+             GUC_REPORT
+         },
+         &intervalstyle_string,
+         "postgres", assign_intervalstyle, NULL
+     },
+
      {
          {"default_tablespace", PGC_USERSET, CLIENT_CONN_STATEMENT,
              gettext_noop("Sets the default tablespace to create tables and indexes in."),
*** a/src/backend/utils/misc/postgresql.conf.sample
--- b/src/backend/utils/misc/postgresql.conf.sample
***************
*** 419,424 ****
--- 419,425 ----
  # - Locale and Formatting -

  #datestyle = 'iso, mdy'
+ #intervalstyle = 'postgres'
  #timezone = unknown            # actually, defaults to TZ environment
                      # setting
  #timezone_abbreviations = 'Default'     # Select the set of available time zone
*** a/src/bin/psql/tab-complete.c
--- b/src/bin/psql/tab-complete.c
***************
*** 1953,1958 **** psql_completion(char *text, int start, int end)
--- 1953,1965 ----

              COMPLETE_WITH_LIST(my_list);
          }
+         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)
          {
              static const char *const my_list[] =
*** a/src/include/commands/variable.h
--- b/src/include/commands/variable.h
***************
*** 15,20 ****
--- 15,22 ----

  extern const char *assign_datestyle(const char *value,
                   bool doit, GucSource source);
+ extern const char *assign_intervalstyle(const char *value,
+                  bool doit, GucSource source);
  extern const char *assign_timezone(const char *value,
                  bool doit, GucSource source);
  extern const char *show_timezone(void);
*** a/src/include/miscadmin.h
--- b/src/include/miscadmin.h
***************
*** 191,196 **** extern PGDLLIMPORT Oid MyDatabaseTableSpace;
--- 191,208 ----

  extern int    DateStyle;
  extern int    DateOrder;
+
+ /*
+  * IntervalStyles
+  *   INTSTYLE_POSTGRES             Like Postgres8.3 when DateStyle = 'iso'
+  *   INTSTYLE_POSTGRES_VERBOSE     Like Postgres8.3 when DateStyle = 'sql'
+  *   INTSTYLE_SQL_STANDARD         SQL standard interval literals
+  */
+ #define INTSTYLE_POSTGRES             0
+ #define INTSTYLE_POSTGRES_VERBOSE     1
+ #define INTSTYLE_SQL_STANDARD         2
+
+ extern int    IntervalStyle;

  /*
   * HasCTZSet is true if user has set timezone as a numeric offset from UTC.
*** a/src/interfaces/libpq/fe-connect.c
--- b/src/interfaces/libpq/fe-connect.c
***************
*** 209,214 **** static const PQEnvironmentOption EnvironmentOptions[] =
--- 209,217 ----
          "PGDATESTYLE", "datestyle"
      },
      {
+         "PGINTERVALSTYLE", "intervalstyle"
+     },
+     {
          "PGTZ", "timezone"
      },
      {
*** a/src/test/regress/expected/interval.out
--- b/src/test/regress/expected/interval.out
***************
*** 2,7 ****
--- 2,8 ----
  -- INTERVAL
  --
  SET DATESTYLE = 'ISO';
+ SET IntervalStyle to postgres;
  -- check acceptance of "time zone style"
  SELECT INTERVAL '01:00' AS "One hour";
   One hour
***************
*** 273,278 **** FROM INTERVAL_MULDIV_TBL;
--- 274,280 ----

  DROP TABLE INTERVAL_MULDIV_TBL;
  SET DATESTYLE = 'postgres';
+ SET IntervalStyle to postgres_verbose;
  SELECT '' AS ten, * FROM INTERVAL_TBL;
   ten |              f1
  -----+-------------------------------
***************
*** 326,331 **** SELECT justify_interval(interval '1 month -1 hour') as "1 month -1 hour";
--- 328,334 ----

  -- test fractional second input, and detection of duplicate units
  SET DATESTYLE = 'ISO';
+ SET IntervalStyle TO postgres;
  SELECT '1 millisecond'::interval, '1 microsecond'::interval,
         '500 seconds 99 milliseconds 51 microseconds'::interval;
     interval   |    interval     |    interval
***************
*** 609,611 **** SELECT interval '1 2:03:04.5678' minute to second(2);
--- 612,648 ----
   00:03:04.57
  (1 row)

+ -- test inputting and outputting SQL standard interval literals
+ SET IntervalStyle TO sql_standard;
+ SELECT  interval '0'                       AS "zero",
+         interval '1-2' year to month       AS "year-month",
+         interval '1 2:03:04' day to second AS "day-time",
+         - interval '1-2'                   AS "negative year-month",
+         - interval '1 2:03:04'             AS "negative day-time";
+  zero | year-month | day-time  | negative year-month | negative day-time
+ ------+------------+-----------+---------------------+-------------------
+  0    | 1-2        | 1 2:03:04 | -1-2                | -1 2:03:04
+ (1 row)
+
+ -- test input of some not-quite-standard interval values in the sql style
+ SET IntervalStyle TO postgres;
+ SELECT  interval '+1 -1:00:00',
+         interval '-1 +1:00:00',
+         interval '+1-2 -3 +4:05:06.789',
+         interval '-1-2 +3 -4:05:06.789';
+     interval     |     interval      |              interval               |                interval
+ -----------------+-------------------+-------------------------------------+----------------------------------------
+  1 day -01:00:00 | -1 days +01:00:00 | 1 year 2 mons -3 days +04:05:06.789 | -1 years -2 mons +3 days -04:05:06.789
+ (1 row)
+
+ -- test output of couple non-standard interval values in the sql style
+ SET IntervalStyle TO sql_standard;
+ SELECT  interval '1 day -1 hours',
+         interval '-1 days +1 hours',
+         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';
+      interval     |     interval     |       interval       |       ?column?
+ ------------------+------------------+----------------------+----------------------
+  +0-0 +1 -1:00:00 | +0-0 -1 +1:00:00 | +1-2 -3 +4:05:06.789 | -1-2 +3 -4:05:06.789
+ (1 row)
+
*** a/src/test/regress/pg_regress.c
--- b/src/test/regress/pg_regress.c
***************
*** 708,713 **** initialize_environment(void)
--- 708,714 ----
       */
      putenv("PGTZ=PST8PDT");
      putenv("PGDATESTYLE=Postgres, MDY");
+     putenv("PGINTERVALSTYLE=postgres_verbose");

      if (temp_install)
      {
*** a/src/test/regress/sql/interval.sql
--- b/src/test/regress/sql/interval.sql
***************
*** 3,8 ****
--- 3,9 ----
  --

  SET DATESTYLE = 'ISO';
+ SET IntervalStyle to postgres;

  -- check acceptance of "time zone style"
  SELECT INTERVAL '01:00' AS "One hour";
***************
*** 94,99 **** FROM INTERVAL_MULDIV_TBL;
--- 95,101 ----
  DROP TABLE INTERVAL_MULDIV_TBL;

  SET DATESTYLE = 'postgres';
+ SET IntervalStyle to postgres_verbose;

  SELECT '' AS ten, * FROM INTERVAL_TBL;

***************
*** 118,123 **** SELECT justify_interval(interval '1 month -1 hour') as "1 month -1 hour";
--- 120,127 ----

  -- test fractional second input, and detection of duplicate units
  SET DATESTYLE = 'ISO';
+ SET IntervalStyle TO postgres;
+
  SELECT '1 millisecond'::interval, '1 microsecond'::interval,
         '500 seconds 99 milliseconds 51 microseconds'::interval;
  SELECT '3 days 5 milliseconds'::interval;
***************
*** 174,176 **** SELECT interval '1 2:03:04.5678' hour to second(2);
--- 178,202 ----
  SELECT interval '1 2.3456' minute to second(2);
  SELECT interval '1 2:03.5678' minute to second(2);
  SELECT interval '1 2:03:04.5678' minute to second(2);
+
+ -- test inputting and outputting SQL standard interval literals
+ SET IntervalStyle TO sql_standard;
+ SELECT  interval '0'                       AS "zero",
+         interval '1-2' year to month       AS "year-month",
+         interval '1 2:03:04' day to second AS "day-time",
+         - interval '1-2'                   AS "negative year-month",
+         - interval '1 2:03:04'             AS "negative day-time";
+
+ -- test input of some not-quite-standard interval values in the sql style
+ SET IntervalStyle TO postgres;
+ SELECT  interval '+1 -1:00:00',
+         interval '-1 +1:00:00',
+         interval '+1-2 -3 +4:05:06.789',
+         interval '-1-2 +3 -4:05:06.789';
+
+ -- test output of couple non-standard interval values in the sql style
+ SET IntervalStyle TO sql_standard;
+ SELECT  interval '1 day -1 hours',
+         interval '-1 days +1 hours',
+         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';