Proposed patch: make SQL interval-literal syntax work per spec

*** a/doc/src/sgml/config.sgml
--- b/doc/src/sgml/config.sgml
***************
*** 4090,4095 **** SET XML OPTION { DOCUMENT | CONTENT };
--- 4090,4117 ----
        </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 output SQL Standard
+         strings when given intervals that conform to the SQL
+         standard (either year-month only or date-time only; and no
+         mixing of positive and negative components).
+         The value <literal>postgres</> will output intervals in
+         a format that matches what old releases had output when
+         the DateStyle was set to <literal>'ISO'</>.
+         The value <literal>postgres_verbose</> will output intervals in
+         a format that matches what old releases had output when
+         the DateStyle was set to <literal>'SQL'</>.
+        </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
***************
*** 2213,2218 **** January 8 04:05:06 1999 PST
--- 2213,2305 ----
      </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 four
+      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 sql standard).   For an interval
+      containing both a year-month and a datetime component, the
+      output will be a SQL Standard unquoted year-month literal
+      string joined to a SQL Standard unquoted datetime literal
+      string with a space in between.
+     </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</>.
+     </para>
+ 
+     <para>
+      The <literal>postgres_verbose</> style will output intervals that match
+      the style PostgreSQL 8.3 outputed 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 years -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 string sliterals when given a strictly SQL-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, "ISO");
+ 	}
+ 	return result;
+ }
+ 
+ 
+ /*
   * TIMEZONE
   */
  
*** a/src/backend/utils/adt/datetime.c
--- b/src/backend/utils/adt/datetime.c
***************
*** 3605,3610 **** EncodeDateTime(struct pg_tm * tm, fsec_t fsec, int *tzp, char **tzn, int style,
--- 3605,3624 ----
  	return TRUE;
  }
  
+ /*
+  * small helper funciton to avoid copy&paste of this ifdef below
+  */
+ void
+ AppendFsec(char * cp,fsec_t fsec) {
+     if (fsec==0) return;
+ #ifdef HAVE_INT64_TIMESTAMP
+     sprintf(cp, ".%06d", Abs(fsec));
+ #else
+     sprintf(cp, ":%012.9f", fabs(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,
--- 3627,3643 ----
   * 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)
--- 3646,3658 ----
  	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",
--- 3660,3738 ----
  	 */
  	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)
! 			                    ? '-' : '+';
! 				sprintf(cp,"%c%d-%d %c%d %c%d:%02d:%02d",
! 				        year_sign,abs(year),abs(mon),
! 				        day_sign,abs(mday),
! 				        sec_sign,abs(hour),abs(min),abs(sec));
! 				AppendFsec(cp+strlen(cp),fsec);
! 			}
! 			else if (has_year_month)
! 			{
! 				sprintf(cp,"%d-%d",year,mon);
! 			}
! 			else if (has_day)
! 			{
! 				sprintf(cp,"%d %d:%02d:%02d",mday,hour,min,sec);
! 				AppendFsec(cp+strlen(cp),fsec);
! 			}
! 			else
! 			{
! 				sprintf(cp,"%d:%02d:%02d",hour,min,sec);
! 				AppendFsec(cp+strlen(cp),fsec);
! 			}
! 			cp += strlen(cp);
! 			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);
--- 3795,3809 ----
  					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() */
  
--- 3930,3948 ----
  					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, INTSTYLE_POSTGRES, DateStyle, 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
***************
*** 367,372 **** static bool session_auth_is_superuser;
--- 367,373 ----
  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;
***************
*** 2098,2103 **** static struct config_string ConfigureNamesString[] =
--- 2099,2113 ----
  		"ISO, MDY", assign_datestyle, NULL
  	},
  
+  	{
+ 		{"IntervalStyle", PGC_USERSET, CLIENT_CONN_LOCALE,
+ 			gettext_noop("Sets the display format for interval values."),
+ 			GUC_LIST_INPUT | 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
***************
*** 426,431 ****
--- 426,432 ----
  # - 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 interals
+  */
+ #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/doc/src/sgml/config.sgml
--- b/doc/src/sgml/config.sgml
***************
*** 4090,4095 **** SET XML OPTION { DOCUMENT | CONTENT };
--- 4090,4117 ----
        </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 output SQL Standard
+         strings when given intervals that conform to the SQL
+         standard (either year-month only or date-time only; and no
+         mixing of positive and negative components).
+         The value <literal>postgres</> will output intervals in
+         a format that matches what old releases had output when
+         the DateStyle was set to <literal>'ISO'</>.
+         The value <literal>postgres_verbose</> will output intervals in
+         a format that matches what old releases had output when
+         the DateStyle was set to <literal>'SQL'</>.
+        </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
***************
*** 2213,2218 **** January 8 04:05:06 1999 PST
--- 2213,2305 ----
      </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 four
+      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 sql standard).   For an interval
+      containing both a year-month and a datetime component, the
+      output will be a SQL Standard unquoted year-month literal
+      string joined to a SQL Standard unquoted datetime literal
+      string with a space in between.
+     </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</>.
+     </para>
+ 
+     <para>
+      The <literal>postgres_verbose</> style will output intervals that match
+      the style PostgreSQL 8.3 outputed 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 years -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 string sliterals when given a strictly SQL-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
***************
*** 3605,3610 **** EncodeDateTime(struct pg_tm * tm, fsec_t fsec, int *tzp, char **tzn, int style,
--- 3605,3624 ----
  	return TRUE;
  }
  
+ /*
+  * small helper funciton to avoid copy&paste of this ifdef below
+  */
+ void
+ AppendFsec(char * cp,fsec_t fsec) {
+     if (fsec==0) return;
+ #ifdef HAVE_INT64_TIMESTAMP
+     sprintf(cp, ".%06d", Abs(fsec));
+ #else
+     sprintf(cp, ":%012.9f", fabs(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,
--- 3627,3643 ----
   * 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)
--- 3646,3658 ----
  	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",
--- 3660,3738 ----
  	 */
  	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)
! 			                    ? '-' : '+';
! 				sprintf(cp,"%c%d-%d %c%d %c%d:%02d:%02d",
! 				        year_sign,abs(year),abs(mon),
! 				        day_sign,abs(mday),
! 				        sec_sign,abs(hour),abs(min),abs(sec));
! 				AppendFsec(cp+strlen(cp),fsec);
! 			}
! 			else if (has_year_month)
! 			{
! 				sprintf(cp,"%d-%d",year,mon);
! 			}
! 			else if (has_day)
! 			{
! 				sprintf(cp,"%d %d:%02d:%02d",mday,hour,min,sec);
! 				AppendFsec(cp+strlen(cp),fsec);
! 			}
! 			else
! 			{
! 				sprintf(cp,"%d:%02d:%02d",hour,min,sec);
! 				AppendFsec(cp+strlen(cp),fsec);
! 			}
! 			cp += strlen(cp);
! 			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);
--- 3795,3809 ----
  					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() */
  
--- 3930,3948 ----
  					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
***************
*** 367,372 **** static bool session_auth_is_superuser;
--- 367,373 ----
  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;
***************
*** 2098,2103 **** static struct config_string ConfigureNamesString[] =
--- 2099,2114 ----
  		"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
***************
*** 426,431 ****
--- 426,432 ----
  # - 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 interals
+  */
+ #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/test/regress/sql/alter_table.sql
--- b/src/test/regress/sql/alter_table.sql
***************
*** 60,65 **** ALTER TABLE tmp ADD COLUMN y float4[];
--- 60,67 ----
  
  ALTER TABLE tmp ADD COLUMN z int2[];
  
+ SET IntervalStyle to postgres_verbose;
+ 
  INSERT INTO tmp (a, b, c, d, e, f, g, h, i, j, k, l, m, n, p, q, r, s, t, u,
  	v, w, x, y, z)
     VALUES (4, 'name', 'text', 4.1, 4.1, 2, '(4.1,4.1,3.1,3.1)', 
*** a/src/test/regress/sql/arrays.sql
--- b/src/test/regress/sql/arrays.sql
***************
*** 283,288 **** select '{ }}'::text[];
--- 283,289 ----
  select array[];
  -- none of the above should be accepted
  
+ SET intervalstyle to postgres_verbose;
  -- all of the following should be accepted
  select '{}'::text[];
  select '{{{1,2,3,4},{2,3,4,5}},{{3,4,5,6},{4,5,6,7}}}'::text[];
*** a/src/test/regress/sql/horology.sql
--- b/src/test/regress/sql/horology.sql
***************
*** 73,79 **** SELECT date '1991-02-03' - time with time zone '04:05:06 UTC' AS "Subtract Time
  --
  -- timestamp, interval arithmetic
  --
! 
  SELECT timestamp without time zone '1996-03-01' - interval '1 second' AS "Feb 29";
  SELECT timestamp without time zone '1999-03-01' - interval '1 second' AS "Feb 28";
  SELECT timestamp without time zone '2000-03-01' - interval '1 second' AS "Feb 29";
--- 73,79 ----
  --
  -- timestamp, interval arithmetic
  --
! set IntervalStyle to postgres_verbose;
  SELECT timestamp without time zone '1996-03-01' - interval '1 second' AS "Feb 29";
  SELECT timestamp without time zone '1999-03-01' - interval '1 second' AS "Feb 28";
  SELECT timestamp without time zone '2000-03-01' - interval '1 second' AS "Feb 29";
*** a/src/test/regress/sql/interval.sql
--- b/src/test/regress/sql/interval.sql
***************
*** 94,99 **** FROM INTERVAL_MULDIV_TBL;
--- 94,100 ----
  DROP TABLE INTERVAL_MULDIV_TBL;
  
  SET DATESTYLE = 'postgres';
+ SET INTERVALSTYLE = 'postgres_verbose';
  
  SELECT '' AS ten, * FROM INTERVAL_TBL;
  
***************
*** 118,123 **** SELECT justify_interval(interval '1 month -1 hour') as "1 month -1 hour";
--- 119,126 ----
  
  -- test fractional second input, and detection of duplicate units
  SET DATESTYLE = 'ISO';
+ SET INTERVALSTYLE = 'postgres';
+ 
  SELECT '1 millisecond'::interval, '1 microsecond'::interval,
         '500 seconds 99 milliseconds 51 microseconds'::interval;
  SELECT '3 days 5 milliseconds'::interval;
*** a/src/test/regress/sql/reltime.sql
--- b/src/test/regress/sql/reltime.sql
***************
*** 2,7 ****
--- 2,10 ----
  -- RELTIME
  --
  
+ -- DateStyle is  'Postgres, MDY' here...
+ SET intervalstyle to postgres_verbose; 
+ 
  CREATE TABLE RELTIME_TBL (f1 reltime);
  
  INSERT INTO RELTIME_TBL (f1) VALUES ('@ 1 minute');
***************
*** 23,29 **** INSERT INTO RELTIME_TBL (f1) VALUES ('badly formatted reltime');
  INSERT INTO RELTIME_TBL (f1) VALUES ('@ 30 eons ago');
  
  -- test reltime operators
- 
  SELECT '' AS six, * FROM RELTIME_TBL;
  
  SELECT '' AS five, * FROM RELTIME_TBL
--- 26,31 ----
*** a/src/test/regress/sql/timestamp.sql
--- b/src/test/regress/sql/timestamp.sql
***************
*** 163,168 **** SELECT '' AS "16", d1 FROM TIMESTAMP_TBL
--- 163,171 ----
  SELECT '' AS "49", d1 FROM TIMESTAMP_TBL
     WHERE d1 >= timestamp without time zone '1997-01-02';
  
+ -- DateStyle was 'Postgres, MDY' at this point.
+ SET intervalstyle to postgres_verbose;
+ 
  SELECT '' AS "54", d1 - timestamp without time zone '1997-01-02' AS diff
     FROM TIMESTAMP_TBL WHERE d1 BETWEEN '1902-01-01' AND '2038-01-01';
  
*** a/src/test/regress/sql/timestamptz.sql
--- b/src/test/regress/sql/timestamptz.sql
***************
*** 182,187 **** SELECT '' AS "16", d1 FROM TIMESTAMPTZ_TBL
--- 182,190 ----
  SELECT '' AS "49", d1 FROM TIMESTAMPTZ_TBL
     WHERE d1 >= timestamp with time zone '1997-01-02';
  
+ -- Datestyle was Postgres, MDY here
+ SET intervalstyle TO postgres_verbose; 
+ 
  SELECT '' AS "54", d1 - timestamp with time zone '1997-01-02' AS diff
     FROM TIMESTAMPTZ_TBL WHERE d1 BETWEEN '1902-01-01' AND '2038-01-01';
  

*** a/doc/src/sgml/config.sgml
--- b/doc/src/sgml/config.sgml
***************
*** 4016,4021 **** SET XML OPTION { DOCUMENT | CONTENT };
--- 4016,4043 ----
        </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 output SQL Standard
+         strings when given intervals that conform to the SQL
+         standard (either year-month only or date-time only; and no
+         mixing of positive and negative components).
+         The value <literal>postgres</> will output intervals in
+         a format that matches what old releases had output when
+         the DateStyle was set to <literal>'ISO'</>.
+         The value <literal>postgres_verbose</> will output intervals in
+         a format that matches what old releases had output when
+         the DateStyle was set to <literal>'SQL'</>.
+        </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
***************
*** 2213,2218 **** January 8 04:05:06 1999 PST
--- 2213,2305 ----
      </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 four
+      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 sql standard).   For an interval
+      containing both a year-month and a datetime component, the
+      output will be a SQL Standard unquoted year-month literal
+      string joined to a SQL Standard unquoted datetime literal
+      string with a space in between.
+     </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</>.
+     </para>
+ 
+     <para>
+      The <literal>postgres_verbose</> style will output intervals that match
+      the style PostgreSQL 8.3 outputed 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 years -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 string sliterals when given a strictly SQL-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
***************
*** 3605,3610 **** EncodeDateTime(struct pg_tm * tm, fsec_t fsec, int *tzp, char **tzn, int style,
--- 3605,3625 ----
  	return TRUE;
  }
  
+ /*
+  * small helper funciton to avoid copy&paste of this ifdef below
+  */
+ static void
+ AppendFsec(char * cp,fsec_t fsec)
+ {
+     if (fsec==0) return;
+ #ifdef HAVE_INT64_TIMESTAMP
+     sprintf(cp, ".%06d", Abs(fsec));
+ #else
+     sprintf(cp, ":%012.9f", fabs(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,
--- 3628,3644 ----
   * 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)
--- 3647,3659 ----
  	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",
--- 3661,3738 ----
  	 */
  	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:%02d",
! 				        year_sign,abs(year),abs(mon),
! 				        day_sign,abs(mday),
! 				        sec_sign,abs(hour),abs(min),abs(sec));
! 				AppendFsec(cp,fsec);
! 			}
! 			else if (has_year_month)
! 			{
! 				sprintf(cp,"%d-%d",year,mon);
! 			}
! 			else if (has_day)
! 			{
! 				cp += sprintf(cp,"%d %d:%02d:%02d",mday,hour,min,sec);
! 				AppendFsec(cp,fsec);
! 			}
! 			else
! 			{
! 				cp += sprintf(cp,"%d:%02d:%02d",hour,min,sec);
! 				AppendFsec(cp,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);
--- 3795,3809 ----
  					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() */
  
--- 3930,3948 ----
  					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/test/regress/sql/alter_table.sql
--- b/src/test/regress/sql/alter_table.sql
***************
*** 60,65 **** ALTER TABLE tmp ADD COLUMN y float4[];
--- 60,67 ----
  
  ALTER TABLE tmp ADD COLUMN z int2[];
  
+ SET IntervalStyle to postgres_verbose;
+ 
  INSERT INTO tmp (a, b, c, d, e, f, g, h, i, j, k, l, m, n, p, q, r, s, t, u,
  	v, w, x, y, z)
     VALUES (4, 'name', 'text', 4.1, 4.1, 2, '(4.1,4.1,3.1,3.1)', 
*** a/src/test/regress/sql/arrays.sql
--- b/src/test/regress/sql/arrays.sql
***************
*** 283,288 **** select '{ }}'::text[];
--- 283,289 ----
  select array[];
  -- none of the above should be accepted
  
+ SET intervalstyle to postgres_verbose;
  -- all of the following should be accepted
  select '{}'::text[];
  select '{{{1,2,3,4},{2,3,4,5}},{{3,4,5,6},{4,5,6,7}}}'::text[];
*** a/src/test/regress/sql/horology.sql
--- b/src/test/regress/sql/horology.sql
***************
*** 73,79 **** SELECT date '1991-02-03' - time with time zone '04:05:06 UTC' AS "Subtract Time
  --
  -- timestamp, interval arithmetic
  --
! 
  SELECT timestamp without time zone '1996-03-01' - interval '1 second' AS "Feb 29";
  SELECT timestamp without time zone '1999-03-01' - interval '1 second' AS "Feb 28";
  SELECT timestamp without time zone '2000-03-01' - interval '1 second' AS "Feb 29";
--- 73,79 ----
  --
  -- timestamp, interval arithmetic
  --
! set IntervalStyle to postgres_verbose;
  SELECT timestamp without time zone '1996-03-01' - interval '1 second' AS "Feb 29";
  SELECT timestamp without time zone '1999-03-01' - interval '1 second' AS "Feb 28";
  SELECT timestamp without time zone '2000-03-01' - interval '1 second' AS "Feb 29";
*** a/src/test/regress/sql/interval.sql
--- b/src/test/regress/sql/interval.sql
***************
*** 94,99 **** FROM INTERVAL_MULDIV_TBL;
--- 94,100 ----
  DROP TABLE INTERVAL_MULDIV_TBL;
  
  SET DATESTYLE = 'postgres';
+ SET INTERVALSTYLE = 'postgres_verbose';
  
  SELECT '' AS ten, * FROM INTERVAL_TBL;
  
***************
*** 118,123 **** SELECT justify_interval(interval '1 month -1 hour') as "1 month -1 hour";
--- 119,126 ----
  
  -- test fractional second input, and detection of duplicate units
  SET DATESTYLE = 'ISO';
+ SET INTERVALSTYLE = 'postgres';
+ 
  SELECT '1 millisecond'::interval, '1 microsecond'::interval,
         '500 seconds 99 milliseconds 51 microseconds'::interval;
  SELECT '3 days 5 milliseconds'::interval;
*** a/src/test/regress/sql/reltime.sql
--- b/src/test/regress/sql/reltime.sql
***************
*** 2,7 ****
--- 2,10 ----
  -- RELTIME
  --
  
+ -- DateStyle is  'Postgres, MDY' here...
+ SET intervalstyle to postgres_verbose; 
+ 
  CREATE TABLE RELTIME_TBL (f1 reltime);
  
  INSERT INTO RELTIME_TBL (f1) VALUES ('@ 1 minute');
***************
*** 23,29 **** INSERT INTO RELTIME_TBL (f1) VALUES ('badly formatted reltime');
  INSERT INTO RELTIME_TBL (f1) VALUES ('@ 30 eons ago');
  
  -- test reltime operators
- 
  SELECT '' AS six, * FROM RELTIME_TBL;
  
  SELECT '' AS five, * FROM RELTIME_TBL
--- 26,31 ----
*** a/src/test/regress/sql/timestamp.sql
--- b/src/test/regress/sql/timestamp.sql
***************
*** 163,168 **** SELECT '' AS "16", d1 FROM TIMESTAMP_TBL
--- 163,171 ----
  SELECT '' AS "49", d1 FROM TIMESTAMP_TBL
     WHERE d1 >= timestamp without time zone '1997-01-02';
  
+ -- DateStyle was 'Postgres, MDY' at this point.
+ SET intervalstyle to postgres_verbose;
+ 
  SELECT '' AS "54", d1 - timestamp without time zone '1997-01-02' AS diff
     FROM TIMESTAMP_TBL WHERE d1 BETWEEN '1902-01-01' AND '2038-01-01';
  
*** a/src/test/regress/sql/timestamptz.sql
--- b/src/test/regress/sql/timestamptz.sql
***************
*** 182,187 **** SELECT '' AS "16", d1 FROM TIMESTAMPTZ_TBL
--- 182,190 ----
  SELECT '' AS "49", d1 FROM TIMESTAMPTZ_TBL
     WHERE d1 >= timestamp with time zone '1997-01-02';
  
+ -- Datestyle was Postgres, MDY here
+ SET intervalstyle TO postgres_verbose; 
+ 
  SELECT '' AS "54", d1 - timestamp with time zone '1997-01-02' AS diff
     FROM TIMESTAMPTZ_TBL WHERE d1 BETWEEN '1902-01-01' AND '2038-01-01';
  

*** 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,2231 **** January 8 04:05:06 1999 PST
  
      <para>
       The output format of the interval types can be set to one of the four
!      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
--- 2240,2247 ----
  
      <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
       linkend="interval-style-output-table"> shows examples of each
***************
*** 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,2283 **** January 8 04:05:06 1999 PST
  		  <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 years -2 mons +3 days -04:05:06</entry>
  		 </row>
  		 <row>
  		  <entry>postgres_verbose</entry>
--- 2306,2321 ----
  		  <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>
! 		  <entry>-1 year -2 mons 3 days -04:05:06</entry>
  		 </row>
  		 <row>
  		  <entry>postgres_verbose</entry>
***************
*** 2293,2299 **** January 8 04:05:06 1999 PST
       Note that <literal>sql_standard</> style will only produce strictly 
       standards-conforming string sliterals when given a strictly SQL-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>
--- 2331,2339 ----
       Note that <literal>sql_standard</> style will only produce strictly 
       standards-conforming string sliterals when given a strictly SQL-standard interval
       value - meaning that it needs to be a pure year-month or datetime
!      interval and not mix positive and negative components, and 
!      <literal>iso_8601</> style will only product strictly ISO-8601 standard
!      strings when given values that can be represented as ISO-8601 intervals.
       </para>
  
     </sect2>
*** 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,2906 ----
  }
  
  
+ /*
+  * 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;
+ 	fval	   *= scale;
+ 	sec		    = fval;
+ 	tm->tm_sec += sec;
+ #ifdef HAVE_INT64_TIMESTAMP
+ 	*fsec	   += ((fval - sec) * 1000000);
+ #else
+ 	*fsec	   += (fval - sec);
+ #endif
+ }
+ 
+ 
+ /* DecodeISO8601Interval()
+  *
+  *  Check if it's a ISO 8601 Section 5.5.4.2 "Representation of
+  *  time-interval by duration only." 
+  *  Basic extended format:  PnYnMnDTnHnMnS
+  *                          PnW
+  *  For more info.
+  *  http://www.astroclark.freeserve.co.uk/iso8601/index.html
+  *  ftp://ftp.qsl.net/pub/g1smd/154N362_.PDF
+  *
+  *  Examples:  P1D  for 1 day
+  *             PT1H for 1 hour
+  *             P2Y6M7DT1H30M for 2 years, 6 months, 7 days 1 hour 30 min
+  *
+  *  The first field is exactly "p" or "pt" it may be of this type.
+  *
+  *  Returns DTERR_BAD_FORMAT if the field is not of this type.
+  *
+  *  It pretty strictly checks the spec, with the two exceptions
+  *  that a week field ('W') may coexist with other units, and that
+  *  this function allows decimals in fields other than the least
+  *  significant units.
+  */
+ int
+ DecodeISO8601Interval(char *str, struct pg_tm * tm, fsec_t *fsec) 
+ {
+ 	char    unit;
+ 	int		fmask = 0,
+ 			tmask;
+ 	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;
+ 
+ 	/*
+ 	 * 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;
+ 	}
+ 	str++;
+ 	
+ 	/*
+ 	 * Process pairs of fields at a time.
+ 	 */
+ 	while (*str)
+ 	{
+ 		if (*str == 'T') 
+ 		{
+ 			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_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 */
+ 			}
+ 		}
+ 		else
+ 		{
+ 			switch (unit) /* H M S */
+ 			{
+ 				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;
+ }
+ 
+ 
+ 
  /* DecodeInterval()
   * Interpret previously parsed fields for general time interval.
   * Returns 0 if successful, DTERR code if bogus input detected.
***************
*** 2732,2737 **** DecodeSpecial(int field, char *lowtoken, int *val)
--- 2910,2919 ----
   *
   * Allow ISO-style time span, with implicit units on number of days
   *	preceding an hh:mm:ss field. - thomas 1998-04-30
+  * 
+  * Allow ISO-8601 style "Representation of time-interval by duration only"
+  *  of the format 'PnYnMnDTnHnMnS' and 'PnW' - ron mayer 2003-08-30
+  *  also deprecate the not-really-iso-like style from 1998-04-30
   */
  int
  DecodeInterval(char **field, int *ftype, int nf, int range,
***************
*** 3605,3613 **** EncodeDateTime(struct pg_tm * tm, fsec_t fsec, int *tzp, char **tzn, int style,
  	return TRUE;
  }
  
! /*
!  * small helper funciton to avoid copy&paste of this ifdef below
   */
  static void
  AppendFsec(char * cp,fsec_t fsec)
  {
--- 3787,3803 ----
  	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
  AppendFsec(char * cp,fsec_t fsec)
  {
***************
*** 3647,3652 **** EncodeInterval(struct pg_tm * tm, fsec_t fsec, int style, char *str)
--- 3837,3846 ----
  	bool		is_nonzero = FALSE;
  	char	   *cp = str;
  
+     /*
+      * These are used so much below, copy them to local variables to make
+      * the code shorter and more readable.
+ 	 */
      int year  = tm->tm_year;
      int mon   = tm->tm_mon;
      int mday  = tm->tm_mday;
***************
*** 3730,3735 **** EncodeInterval(struct pg_tm * tm, fsec_t fsec, int style, char *str)
--- 3924,3957 ----
  			}
  			break;
  		}
+ 		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, "%d", sec);
+ 				AppendFsec(cp,fsec);
+ 				TrimTrailingZeros(cp);
+ 				cp += strlen(cp);
+ 				cp += sprintf(cp,"S");
+ 			}
+ 			break;
  
  		/* compatible with postgresql 8.3 when DateStyle = 'iso' */
  		case INTSTYLE_POSTGRES:
*** 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/doc/src/sgml/config.sgml
--- b/doc/src/sgml/config.sgml
***************
*** 4016,4021 **** SET XML OPTION { DOCUMENT | CONTENT };
--- 4016,4043 ----
        </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 output SQL Standard
+         strings when given intervals that conform to the SQL
+         standard (either year-month only or date-time only; and no
+         mixing of positive and negative components).
+         The value <literal>postgres</> will output intervals in
+         a format that matches what old releases had output when
+         the DateStyle was set to <literal>'ISO'</>.
+         The value <literal>postgres_verbose</> will output intervals in
+         a format that matches what old releases had output when
+         the DateStyle was set to <literal>'SQL'</>.
+        </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
***************
*** 2213,2218 **** January 8 04:05:06 1999 PST
--- 2213,2305 ----
      </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 sql standard).   For an interval
+      containing both a year-month and a datetime component, the
+      output will be a SQL Standard unquoted year-month literal
+      string joined to a SQL Standard unquoted datetime literal
+      string with a space in between.
+     </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</>.
+     </para>
+ 
+     <para>
+      The <literal>postgres_verbose</> style will output intervals that match
+      the style PostgreSQL 8.3 outputed 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 SQL-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", 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,634 ----
   00:03:04.57
  (1 row)
  
+ -- test 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 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';
+        interval       |       ?column?       
+ ----------------------+----------------------
+  +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,192 ----
  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 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 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';

*** 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";

*** 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';

diff --git a/src/backend/utils/adt/datetime.c b/src/backend/utils/adt/datetime.c
index 320496e..70efc6a 100644
--- a/src/backend/utils/adt/datetime.c
+++ b/src/backend/utils/adt/datetime.c
@@ -2802,7 +2802,7 @@ DecodeISO8601Interval(char *str, struct pg_tm * tm, fsec_t *fsec)
 			str++;
 			continue;
 		}
-		if (!(isdigit(*str) || *str == '-' || *str == '.') )
+		if (!(isdigit(*str) || *str == '-' || *str == '.'))
 			return DTERR_BAD_FORMAT;
 
 		fval     = strtod(str, &newstr);
@@ -2833,9 +2833,10 @@ DecodeISO8601Interval(char *str, struct pg_tm * tm, fsec_t *fsec)
 					if (fval != 0)
 						tm->tm_mon += (fval * 12);
 					break;
-				case 'T': /* ISO 8601 5.5.4.2.2 Alternative Format / Basic */
+				case 'T': /* ISO 8601 4.4.3.3 Alternative Format / Basic */
 				case 0:
-					if (numlen == 8) {
+					if (numlen == 8)
+					{
 						tm->tm_year += val / 10000;
 						tm->tm_mon  += (val / 100) % 100;
 						tm->tm_mday += val % 100;
@@ -2845,7 +2846,7 @@ DecodeISO8601Interval(char *str, struct pg_tm * tm, fsec_t *fsec)
 							return 0;
 						break;
 					} /* Else fall through to extended alternative format */
-				case '-': /* ISO 8601 5.5.4.2.2 Alternative Format, Extended */
+				case '-': /* ISO 8601 4.4.3.3 Alternative Format, Extended */
 					tm->tm_year += val;
 					tm->tm_mon  += (fval * 12);
 					if (unit == 0)
@@ -2891,15 +2892,16 @@ DecodeISO8601Interval(char *str, struct pg_tm * tm, fsec_t *fsec)
 					tm->tm_hour += val;
 					AdjustFractionalSeconds(fval, tm, fsec, SECS_PER_HOUR);
 					break;
-				case 0: /* ISO 8601 5.5.4.2.2 Alternative Format */
-				    if (numlen == 6) {
+				case 0: /* ISO 8601 4.4.3.3 Alternative Format */
+				    if (numlen == 6)
+					{
 				    	tm->tm_hour += val / 10000;
 				    	tm->tm_min  += (val / 100) % 100;
 				    	tm->tm_sec  += val % 100;
 				    	AdjustFractionalSeconds(fval, tm, fsec, 1);
 				    	return 0;
 					} /* Else fall through to extended alternative format */
-				case ':': /* ISO 8601 5.5.4.2.2 Alternative Format, Extended */
+				case ':': /* ISO 8601 4.4.3.3 Alternative Format, Extended */
 					tm->tm_hour += val;
 					AdjustFractionalSeconds(fval, tm, fsec, SECS_PER_HOUR);
 					if (unit == 0)

diff --git a/src/backend/utils/adt/datetime.c b/src/backend/utils/adt/datetime.c
index 5d4f6de..7c857df 100644
--- a/src/backend/utils/adt/datetime.c
+++ b/src/backend/utils/adt/datetime.c
@@ -3523,7 +3523,7 @@ EncodeTimeOnly(struct pg_tm * tm, fsec_t fsec, int *tzp, int style, char *str)
 	 * should be equal to the larger of MAX_TIME_PRECISION and
 	 * MAX_TIMESTAMP_PRECISION.
 	 */
-	AppendSeconds(str+strlen(str), tm->tm_sec, fsec, true);
+	AppendSeconds(str + strlen(str), tm->tm_sec, fsec, true);
 
 	if (tzp != NULL)
 		EncodeTimezone(str, *tzp, style);
@@ -3579,9 +3579,10 @@ EncodeDateTime(struct pg_tm * tm, fsec_t fsec, int *tzp, char **tzn, int style,
 			 * since it's unlikely there's any precision left ...
 			 */
 #ifdef HAVE_INT64_TIMESTAMP
-			if (tm->tm_year <= 0) fsec = 0;
+			if (tm->tm_year <= 0)
+				fsec = 0;
 #endif
-			AppendSeconds(str+strlen(str), tm->tm_sec, fsec, true);
+			AppendSeconds(str + strlen(str), tm->tm_sec, fsec, true);
 
 			/*
 			 * tzp == NULL indicates that we don't want *any* time zone info
@@ -3616,9 +3617,10 @@ EncodeDateTime(struct pg_tm * tm, fsec_t fsec, int *tzp, char **tzn, int style,
 			 * since it's unlikely there's any precision left ...
 			 */
 #ifdef HAVE_INT64_TIMESTAMP
-			if (tm->tm_year <= 0) fsec = 0;
+			if (tm->tm_year <= 0)
+				fsec = 0;
 #endif
-			AppendSeconds(str+strlen(str), tm->tm_sec, fsec, true);
+			AppendSeconds(str + strlen(str), tm->tm_sec, fsec, true);
 
 			if (tzp != NULL && tm->tm_isdst >= 0)
 			{
@@ -3649,9 +3651,10 @@ EncodeDateTime(struct pg_tm * tm, fsec_t fsec, int *tzp, char **tzn, int style,
 			 * since it's unlikely there's any precision left ...
 			 */
 #ifdef HAVE_INT64_TIMESTAMP
-			if (tm->tm_year <= 0) fsec = 0;
+			if (tm->tm_year <= 0)
+				fsec = 0;
 #endif
-			AppendSeconds(str+strlen(str), tm->tm_sec, fsec, true);
+			AppendSeconds(str + strlen(str), tm->tm_sec, fsec, true);
 
 			if (tzp != NULL && tm->tm_isdst >= 0)
 			{
@@ -3690,9 +3693,10 @@ EncodeDateTime(struct pg_tm * tm, fsec_t fsec, int *tzp, char **tzn, int style,
 			 * since it's unlikely there's any precision left ...
 			 */
 #ifdef HAVE_INT64_TIMESTAMP
-			if (tm->tm_year <= 0) fsec = 0;
+			if (tm->tm_year <= 0)
+				fsec = 0;
 #endif
-			AppendSeconds(str+strlen(str), tm->tm_sec, fsec, true);
+			AppendSeconds(str + strlen(str), tm->tm_sec, fsec, true);
 
 			sprintf(str + strlen(str), " %04d",
 					(tm->tm_year > 0) ? tm->tm_year : -(tm->tm_year - 1));
@@ -3730,14 +3734,15 @@ EncodeDateTime(struct pg_tm * tm, fsec_t fsec, int *tzp, char **tzn, int style,
 
 static char * 
 AddVerboseIntPart(char * cp, int value, char *units,
-                       bool * is_zero, bool *is_before)
+				  bool * is_zero, bool *is_before)
 {
-	if (value==0) return cp;
+	if (value == 0)
+		return cp;
 	cp += sprintf(cp, " ");
 	if (*is_zero)
 		*is_before = (value < 0);
 	*is_zero = FALSE;
-	if ( (value < 0) ^ (*is_before) )
+	if ((value < 0) ^ (*is_before))
 		cp += sprintf(cp, "-");
 	value = abs(value);
 	cp += sprintf(cp, "%d %s%s", value, units, (value == 1) ? "" : "s");
@@ -3746,9 +3751,10 @@ AddVerboseIntPart(char * cp, int value, char *units,
 
 static char * 
 AddPostgresIntPart(char * cp, int value, char *units,
-                            bool * is_zero, bool *is_before)
+				   bool * is_zero, bool *is_before)
 {
-	if (value==0) return cp;
+	if (value == 0)
+		return cp;
 	cp += sprintf(cp, "%s%s%d %s%s",
 				  (!*is_zero) ? " " : "",
 				  (*is_before && value > 0) ? "+" : "",
@@ -3936,9 +3942,9 @@ EncodeInterval(struct pg_tm * tm, fsec_t fsec, int style, char *str)
 			{
 				int	minus = (hour < 0 || min < 0 || sec < 0 || fsec < 0);
 				cp += sprintf(cp, "%s%s%02d:%02d:",
-						is_zero ? "" : " ",
-						minus ? "-" : is_before ? "+" : "",
-						abs(hour), abs(min));
+							  is_zero ? "" : " ",
+							  minus ? "-" : is_before ? "+" : "",
+							  abs(hour), abs(min));
 				AppendSeconds(cp, sec, fsec, true);
 			}
 			break;

���I�}�w�6����_�����X��-��t�6�&����j��{���m�U��"�l������AR�,����%3�|`f�'O���q�_�������*�����$>���0���G���n]�W{������:���(�G�t������p���/0����h��D��CJ���(��}}����=�F�dz5�������>D2��G�)��-!�HR�������F�\�ct{u��'(�9x�����N�p9�.G��t~��p����������oo1��0���������(w�%��<�xw.����o��������h����b�K����{��	���1*i���,45�b������rS�LF���������v�������;��2��k��Z.�y<�j�n�qYw���~{��?~��)��GGGhF�Y�K0��t�����|us.#�i_���l����4�.�G�8�!��IGp����><x0���,�����#�����Y�������-./Q�Q��<D�3�6d�������-���T�����:��%�!"��q�A{�e�{��3d���&���t�!�)��$��5���X���?��X������^R-&��X���c�F����T�y�;��#����)���O2�iG��}��?�l�{����}������n�)��%��������-9$�g��0�Q���>�!�����pb�L�a!l�c�#nkD�#�(��la�E���#���6r��M�h�'<���!m�tz�:�1;q��i�[�JC)�]\��-.S2�-�zeq;5���.N�q;��>�/l.:���fl����I1jf�O��y��`P%�l��s���[�C�M?`�.�_
�0
��T`�f��X�Q��M�X���U���
\7��S��
��ob��������g�&�O�g7Xor��MV�]/Vy�i7�>��@�������|�1N���A�/��[L���t��f���h�ID�r�DW�<\�qA���b��>��j������V�qEx4v�������JJ.W+<>��+2>�p�yT�"�7peI:��	W������}�_�����*��6�>xp���2-y�dB 0�^�������sl�h	���o�}8=1����Led�>JD�H#�G�*���#&P���q�
�d<�\]S������h���'����7��E�9��!](�
�!�0O1��%�O�}K�@�nW����%�����K�5t�l�$������3���(�D�����cR��?��X��GQH��������p�����|��������;�zsz6|��=]
�S��g�Lx��v���
��p���r5<6��<M
b�M��\xo�~�{���iR��7!'����_��C�!^U#��:%�U=��%���j}JI�9�%D^�6�������t����%��>c#{��������+�6��d:	�t5"��L�x�@��x��;S����6�V�Z���'C$�`��9k^����i'�Jt�B�i�Z�7.�7-hZa7I���u���������5a[5�|���4�?2���#[0:������_c����?f����:;��7�b~����Ls�(9V��P�#m�K0�G�|0��a��S���*���|�������=��6�6��dS"����
���}��lO�g_�S$�������\N�'A�rz��&I����K#�W��c,�^(J�.���x����W���[PX�q��E�T�IzUZC�j+P����u�z����e^���7,R�6�.����)	�m_����*J�}�����	��A�$tA>�xA�[��TQ����TV?�p��*�S'�;�q�t9k�xrR���^���*���u3k����p��63���:�������w����n�*\�2���|G�\��hs��/!q1�nB�wf�&r�@��0�����(Z���py9[|"E��^���h�=����BzXQ�L2�F�3C���1ny\�w;����<#�e�L�^�>�p87�`q��<B���=G������E����b����8����C^�%r�9X��~�h�����u�@"�^�@�����F|�}@11'x������0�����H�X7
s�8c��I�In����������Qjx%7	[1Gl��
�'����A_�����z����>X��4vvv����?��������������-��|2G/(s��@��t�0���N���`��~�2(^h�6tq�i��|�+'P�=cc/L�6Tm}�iv�B,;
"RA��w�����g������X��bt��E�������X��L#}�L����	a��7!�m��~�.�p��Y�S#�9|�6�O�:4B���J��'_S���������d���q��S'���3���=q7���_Xb�Xr����3b�<�k����3�i���.�.���O5f��I��O:��D������
�
Y��Uh�����+	�s=�mW���|k���J�q�l��A��u�X��SE�����%/s����WY�Y�l=�wtVnV�fj>�5�9��6l�"�4t�O�x��'��9�$7����y>�}i��z��r}[��5E�yi�������v����+��4�/^�L������O��Y���s�/f��f�D ����.��2
$�'�����.�q'���a��W�C:�c��&���p�g\��C6J���1Z��^f��e��]�M�W��
w�%�m�$�������yf�6J�^�d������W2T�,�*ov��}��n���dKh�9Z��,,���3����LI������!I/���O6Hf����D�����3��X~�-"v(��PB8][��#��u�����}c9���^G����cIJ ����_���4A+�a�S�z���:���b}"+�]���#�p[��"D�?W$�e�2�FK��"�Wo��_r������^�{K�`9�(��#i�#V�d�Q��#9��,Eb:�s�0�(�����c��aUBRJ����m]f����O��O/K2�h$�E'����V?���?�~�4'������SR��|\6���%��mM=�:U5�%���l��r�+C�1����}���,r���1�����s��1�J7�x
K�}i�%��t�E�|]���;#�8������wX��g"�MUo�%*C�C��K���T�#���$?:A�L����q�,��NF�������5S��[�c>�IXF��]
���I���\0�{>������]O������o��3���? �1I�5���1I4�DsJ�F��|���lN0����D2=!�U��:78
����L��^a�,��&�
x�y�f,��t�������S�&�{�iD4K�	�V&��|��6��a��w%��e���g) Z�Y���� ����OVa}���(�������guF5LbM�j���������k���	�gY��Y��aS4�l�H��8.Vq��3�;so4���B�pM����s������#����J���F����F#k<o��S&��]3����2�.^+�<%��U���L���~?�����n�L�������4�L�A����W��VrK�7n��������K�j�����-�K���~����KU��u���G<�}�t��*�r�����&J���d�������Y���V�9�>���`^h���q�u������!�|�%��/��m�D������/��^�8Y����i�o����Jn$�S�mb�r��>f���\��d|��$|��C���� M�H�)a�c��{D��U��#�VN$�R�d������;�u<�rD��D	�x?Z�<�B\AL����X�����s?����2�Ms��m��Q�9�./Q�nQ|����S~���\��H�L�}����c3�_�+��L��-�G"�hyB�#~y4n����^&}���K��$����]�K��������?��=�G�9f
�	����	�F�.�����>;MpH��2�����]��w4�����N��z�76��o�c�6�{{x��E}��ROI�M��=GWC��J�p.a`���G��	�e�\.���Y������9u�o�@��GBN�OU�����"\�v�5���Z�������d�c=���x�J��dw����7�����YN07s�4���C�f~mm��}��E���t��eDc��,q����T6_���T�0���Z��%��#���,����Z��#L!�
0snF����WxV�L�0�<p,<s:���U�8I�?�B�^�}`7+��J�~������<8����������x��������4J���4�&_��T�tQT�����W"%����!]����7�.1Bc�_���ABh�N��
�J��REA/$����^��.sJ��MB�I?��{'�����9�u{��?��&TEL���a���<��:�#�?c�)h-���i����
}}z������N�N�;�G�O)��+R�N�BX|'�M������({���2;+d��R���\���QE�����W�*]SI���� �qf! 5�[
(��J��g����CF-�=�j}��vJ�CO<��5�!a�����TM�����1`�oA����E��d-���>k"P����j-�����}�e"e�����"I��4���ZJtp�*�*K���z���)��BQ?��u:	�x�2��
������[zM^��;�����������#(^�G1��e�[$o�+�j��G ���������[�&��h5���@�}����;�t\�_��Qq~y�TW���'M���v��y�~��w�g">��-BiM�5io���X��D:�����f����l�#��%�5miG����S���M�LsIB�����s�<{�b�����*\�2%fy�L�Z�k���5�,&���mWA�g3�`�3=6K-�i��	-�zj�S�fLk���j3��fLKl��l3�����em�V�xj���%3�����M�m��j`vt�A�H,����������W�%�#�w[�[��qR����<����S�W�fs�E#��T�
y]HV��RBz�2�
O�?�4��|���9��r�T%�p��G���
H>�]ke�����r��i�Wo��#�c0rb���D������1e�����q�*���E����9�6Y20�4��������f2�:x�eQ!]-�n
��n�D�WA�����X��7��|�=�����G�`��Sr����O����0�����5�P������pwd����4�:��F��2vL>�d>@��09�D����B���T'������+�~	]��F]�dC/���Q�ki�IuE�!
��
	���#� g�����C3F�G9�-
L�Lz��Er<B&D�2
��8��&��q��R�
��G�LqX4������
zL"Y���6������49��c���<��F���L`�3�njz���=��x�a����/-=�^,������*�����=�r����������?��>z~����lt�v��th���<d��?�{��d����	1��
W!�1���id�N\����=�����
��1yVyP'����q9E�g4G��uB�WpO,������	�x�����A7�?�r��o����r��b]_�u*8���b\��u|@���v��V#}I8z�rsad���X� ����N����	zAs�{�7��F����Vr�J;^Z���|��lRa%�.�yA����o��@.�|g<�
��;���^ss�:L�m��5+9i%��PZ���|���[a%���yA��h��$��\2��x�&�����0�����!����%�:�������������$�F������.�����)���������x�
V���?<���@��E����bL�(OD}�|�U�}xq�
}�+�;�S���g~��O����I���A�T����>���5�B�/�����+�����miP0���t�5�R;Ya���$�bq�\M�\J�^�X�����B�RjWi#�C���C���A��7xj ��t(5:�R�J)RM
2��7�9���������T�PJ�*m�tH55t�xrz��"-+��RS��l�]�
I���:����t��C�{���~�K5�lM�T��������L��
�T�A�L��C�igk�=pO��(��������)�1�J�6����\���TJ]Q
iCOG?W3+��T���z�HJM7[S)�e^�����&���6�R}E�#O�m�����kc����]�6�}����
u
�kc����]�6vm��6�iH�|��jj�ZQ
i�HJA3JiQ3>��H�o�L������LM`�&�7c�F��R�o�
���mf�8�6L��!�
/&-+�0*5���R�Y����������/�QG�8e���T����J�9O������Y�)xt�RZ���#[S�G����F�D���C���ANh�WF�RS�C)����=�55t�xT:��S����B�RjWi#�C���C���c�m@PJG��JG����6$^�5u���T�Um�SNW
�C9�UtW9����	��
����J��������OP���9������$����K�����Rin4���y��yP�1����7��7��kCO�k�����9%.�Cs-��9���9�-���$:Y3hN��%��hi��9���]�hN�5m�������^i� ]��ci#k�4th��K�-QO�����J=QOk*��(����W����
�T���2<�[��MOw�t(5�lM����6�z%j�r5����x�eK�j.-�t����������������p�R�
Y�WJM?[S)u�d�kCK���j���m�f4��t�L�\}6�+����^�f^��m0L��:�^9�6�t�s5�rM� kH�&��lM�^eK������zW���62�
��6+����_H���^jCJ/�!��!�$HX�&A�wc

1�aZ`
�/5�D~��Kk���CKkG��Kk����JkG���Jk���CJkG��Jk����IkG���Ik���CIkG��Ik����HkG���Hk���CHkG��Hk����GkG���Gk���CGkG���rj���Z���ka����]w�(��vL���]�v-�Z��������OQQ3��B@%���6���A�P:|�}{���8O�6(����FI���S�������������������������q�����Q�����1�������������p�����`�����P�����@��q������i
^Y;���������������������������q�����Q�����1�������������p�����`�����P�����@��q��0��Q�� ��1��������������������������������q�����Q�����1�������������p�����`�����P�����@�����]$��}����t�.�����;L���y���zL�0�Y��u|@�A,:Sp�� G���K�bS�'q�w�D
,����~�qzn�>O��Q�I]��J��,s�i����}sq`)
�]���2�e#1T;^f�sc|1����V��U8��:�8�
G��dR8�9��c���1�ZY�X
��p�b�\,)�e��8�[�L����p	/3
�1���b�J+L�*��[�__�p~�p�j��
Gs���U�����b���`���j��������}:��:�B�����*j"���}L����qP����9�{`����b��&�����4p1��ves86����YX�Lbo.�v�b�\,���1��
�+�,�a
1T��f�nS|1����V������e6�f*��?.^��:SE}�vx�������������ue��!�����uN��e�������b�=����lu�@#r8M�,��`��9F�����j�=\|�`��A���@
-%�=��	�N���hh�����W.@`N�D|D�yg�EI�nR��x2O}N�5����@�����X{S~a�����'�qN�����"�@�>x����gu/������`��K�7A�~-d���P	�^�n��/��E]��>��thp�x���/�O,�k�9�
N�%F��3�����NB%��x��!�!����f�u�|�|�F�,$I��+��}T�!k	�������c�%f]g?��?y4�08��p��]N��$2�������+��hz5����T���f^����p�������������}r1���c��_1�����]�?)}|.������E���C�z����d�k�u�2���	'��3������I��C����;K�G�;gI=��z/�7��)�.~��^v�7��y�����M��
??��,$�,oWq<�_�Wg�P?�[1���sd����Q�~!�)NoB��K�EH�>����� ������q���V��Q��>nC�X��m�����N��Y����Z�����B��V��0�����������}��2
�$������;�K�?���Qaj?��EV��5
Ur�"����E8�0d�����q�R���}/->$�.:L���%O�b��}�1�Veuv���������67�\_));�S�	Y�����6�vZ��(]S:����^'�m�e9�I4�Jz1�}�ZD�����N^l��N��c����/������CRZ�J�b���.w�Hl=L�:B���v���|B�;Ea��k<'��G�+:���!����1z����s������x�1���+����3F/�8�9���b�OqE�����cB�4���"��'A��P�~HK�%��/���O"p���xXFht��0���HG�-Y��%��#�i��&�)���y�^N��#���Z�k'^�v�g�!�L->��bl��-��(Rj��PH>,��h������U����-�G7� �g���(����qK����}��T��`6U��f�����:N���bY������8D��9]�`���:��pI�>����!e�g�2�
��d��Oxk�G}�~_�CzS#?{��+:���������e���WgN��S���|�*��QZM�@�Th��=���l���m�V�1r�~���1r��������b"���z���<����Aw�Vo�z�*���n��9�ZX�
}�<�5��~��x���q�|)����M���IW�C��F���u���&��F+:l[����_xe�J��WN;l����=V�W��p��a.����^FW�s6��e�8����n���q"$�'�'��t��6�R��Q����"��&x�~~;Z���>ns����
�G'SJz��_���8L���YD����a���(]�H/����n�?����W=�uH>_�'�;���!�<���_��y���a���<L�$J��*ly��gxz�K>6���G����������^wF>�k3�x�:��Q�N-�d�P
��m�ZT��C6"�0y���� ����S�=��0�Q2��T�}�>���EJ(Frc�Yx��^}08q,r3�8��,nm�D:�-����Z:����IF����>��W������V������h1��7V���at�T��(WDu���c�]{E�}�������`��s��#����E�B�}�����0��
���0:�>������c4��>��+�h~3|��h|�ycp
od����Z��>�
L�o��b�z����`d��Ga�[�t����oH�W`4��^����o��0�%c~Ox�.��s�|,~w6�V��-���,k�{fS��>�1��c~?S�G����f������Pr��Y��M�%���A��TZ%���y�x��7�CD����J�����?I����%����F����B��w�{ ���x�^9_����r�A�Yf�@�f?����L����C`v��cB`������%���!	f����h0��=�#p
$��;�#����{����6��Cp���c4i�����
@`v^@z`�U�]fO��M�	f�B)��@);
�>���T��%@`�T�D������{���y �w���:�yoa�q+��y�m�AS�i� ��?�n�I�wZ���E�,>IQ0�O�f���o47��B�
�a��um��r�X����;C�z�P�Y�����_4���n�%$�pJ�����"�M��n�P��R'c`������2�t����������a��/T������0P�F�t[��p�m��@��tkZl`�l��UA��P���
�f��TM�����
�g��q����v�t���V�j��n2�c�A
���*h�����
�
3n)���|Y�UD�6Rus�T��LAUZK��*h���0^�����*h�V�F�������ETA�#��9LfuZa[���
m�m�]4|w�������x����������04���xC�Qw��/4�� 0A�&/TGX��P!(u������V�
��u��&rOYh<c������OWh<[aK��:h<UakI�!�!�4��6_4�LPh<?������ONh<7�����3OLh</������OJh<'�����3OHh<��t���OFh<a�����k�L��D��F�f�k�)��w����d�$������������������$����#�o���a����n�
��F7`��6|���|���{����������n���gn��)
�7�  ��\x��
ot(@�M��
����u�_������>���-�7��O�o�_�6"?��H�&o�s��[l��)�Q�7n�
���3
�M(X#��v�V:
���
���!��mfv�� ���
�l�+�P�R��-��y�Y��M �dP�Z	�z�@H���V�@H�~�V��u�h�m��� ��l�YlM�k���T!A�ZHP���*��e
h!A�ZH����l�h���B�����
�:
x���eh!A	
d��-$(=@@V��B��d��-$(5 ;gW������
�����W{,��}�f�u��I�o��
�2X��l�n%'�d��X�z>�^`�;W�#)���|�yAD*DB����f ��L�3k���:�����*��-yYh�7��_���0�~���}��������PA����VP��n����kQ����s��������j�%�������z���(����;����_{���O��V}�i���&�{�v����U����|�����UyM���w��P&����^F-�u���
�{2H��v�d��d�������;$�����t�*i�Ke���-�NW�Q�B�%H�!��,X
���.d�D>d�[i$��<��l���:���^�K7����Xa����pg����_\S����5�B_�ka��y��_k�`m�n��k^p��U�++����jo��21g�Xk]uS�z��-���b�sX��k�����s/8��j
�`E��+6U������j����az`�E����V��x��c�Q��X�����:W�c]�~�b��\�S�u��z���s]�ZX#-e��]�S�u��{tX���G���{|���s�O��h�}
5k�k}tX���G���~���s������~tk�����`m��k��P&�����Z�6��*�l
�-@��D���v����}�!Q��:�'f�5�'5^Q�GR�5�y$U/
���H��><	I�+W�@O�
I���}��%� �w�Pr�}g��B�2	I���tH ��a�\�k��m&�kAR����H
�G$��"�q�6V�b�z�U��f�I���X�[����@R��� )��l
I���t�.@���+$����wWR$���
[�
j��q���������
@R���0��k
�I������|�]�u�D��6k}�MK /�z����,����o����9Tj=�m���3T�y��A���J�nd��o��7	�$����d��6�����T�%�r���?P�����>��9����v���^�����{��i�l���:M@�����
K[�gW�
�J��
�K��
7L��
�L��
wM��+����A;q���S&p��S&p�%T��Q�x
8�:*8�N*=8�b*8�v*��U&p�=U
x���L�������U
������Rk�uD�}��?D>�&����&j%���(�VR��5�TC�_y])���U����Z�"j%�h��t[��x&Z�j%}����V��Z�j%u����-R��J+iC[M�����Jj�v"�y�Z�j%Y��\�VR�Z�j%Q��<�V��Z�j%I���VR�Z�j%A����V��Z�j%9����VR�Z�j%1����V��vYA��H
j$'Htu�� 
�NPuMN!�o
OBd�~T[xG�7��q�K��(�m�'N�Q3'���np�:��g�, �c�$ -�0��+uxI8jfep�L���^}�o/-_�/�~����8J=>��N���6e����Ro
��F�G�/�����Q���R?J��M�r1zI����a�u�|@8J]����
�S�C"�2��FwG�w���
sF����C?���=������C�W;��C]�����y�A��s>�j������'(��x$)���)��.yo��O����P���<�^&O��]~�<�
�E;��Y<�5��&=�*���[]�#/�
J���e���Sb����e;o����'�h��9;Zhp���������.�������:Zhp�����]�T��@�Su���L�b����#����h��Y:�^�����]yTM�����:o���9;���p�q4;�?��*���}���w���+
g�8FH@����`g�>dgN������^����J_�,>
��mumh�������vs;[���^G�jh[�nQT4a�������'�,�g���C��a�[����Y����O��+4E!��5V���h~�U?���!����1z�����Wo��w��
�0���!z��s�(��]D1����py��B�:�G�u�<��u]���C����+8�Y�Z�e59��� �
����{�������}�����s��'FE�O���������

*** a/src/interfaces/ecpg/pgtypeslib/dt.h
--- b/src/interfaces/ecpg/pgtypeslib/dt.h
***************
*** 25,30 **** typedef double fsec_t;
--- 25,46 ----
  #define USE_SQL_DATES					2
  #define USE_GERMAN_DATES				3
  
+ #define INTSTYLE_POSTGRES             0
+ #define INTSTYLE_POSTGRES_VERBOSE     1
+ #define INTSTYLE_SQL_STANDARD         2
+ #define INTSTYLE_ISO_8601             3
+ 
+ #define INTERVAL_FULL_RANGE (0x7FFF)
+ #define INTERVAL_MASK(b) (1 << (b))
+ #define MAX_INTERVAL_PRECISION 6
+ 
+ #define DTERR_BAD_FORMAT		(-1)
+ #define DTERR_FIELD_OVERFLOW	(-2)
+ #define DTERR_MD_FIELD_OVERFLOW (-3)	/* triggers hint about DateStyle */
+ #define DTERR_INTERVAL_OVERFLOW (-4)
+ #define DTERR_TZDISP_OVERFLOW	(-5)
+ 
+ 
  #define DAGO			"ago"
  #define EPOCH			"epoch"
  #define INVALID			"invalid"
***************
*** 77,82 **** typedef double fsec_t;
--- 93,101 ----
   * Furthermore, the values for YEAR, MONTH, DAY, HOUR, MINUTE, SECOND
   * must be in the range 0..14 so that the associated bitmasks can fit
   * into the left half of an INTERVAL's typmod value.
+  *
+  * Copy&pasted these values from src/include/utils/datetime.h
+  * 2008-11-20, changing a number of their values.
   */
  
  #define RESERV	0
***************
*** 92,111 **** typedef double fsec_t;
  #define HOUR	10
  #define MINUTE	11
  #define SECOND	12
! #define DOY		13
! #define DOW		14
! #define UNITS	15
! #define ADBC	16
  /* these are only for relative dates */
! #define AGO		17
! #define ABS_BEFORE		18
! #define ABS_AFTER		19
  /* generic fields to help with parsing */
! #define ISODATE 20
! #define ISOTIME 21
  /* reserved for unrecognized string values */
  #define UNKNOWN_FIELD	31
  
  /*
   * Token field definitions for time parsing and decoding.
   * These need to fit into the datetkn table type.
--- 111,133 ----
  #define HOUR	10
  #define MINUTE	11
  #define SECOND	12
! #define MILLISECOND 13
! #define MICROSECOND 14
! #define DOY		15
! #define DOW		16
! #define UNITS	17
! #define ADBC	18
  /* these are only for relative dates */
! #define AGO		19
! #define ABS_BEFORE		20
! #define ABS_AFTER		21
  /* generic fields to help with parsing */
! #define ISODATE 22
! #define ISOTIME 23
  /* reserved for unrecognized string values */
  #define UNKNOWN_FIELD	31
  
+ 
  /*
   * Token field definitions for time parsing and decoding.
   * These need to fit into the datetkn table type.
***************
*** 164,176 **** typedef double fsec_t;
  /*
   * Bit mask definitions for time parsing.
   */
! 
  #define DTK_M(t)		(0x01 << (t))
! 
  #define DTK_DATE_M		(DTK_M(YEAR) | DTK_M(MONTH) | DTK_M(DAY))
  #define DTK_TIME_M		(DTK_M(HOUR) | DTK_M(MINUTE) | DTK_M(SECOND))
  
! #define MAXDATELEN		51		/* maximum possible length of an input date
  								 * string (not counting tr. null) */
  #define MAXDATEFIELDS	25		/* maximum possible number of fields in a date
  								 * string */
--- 186,198 ----
  /*
   * Bit mask definitions for time parsing.
   */
! /* Copy&pasted these values from src/include/utils/datetime.h */
  #define DTK_M(t)		(0x01 << (t))
! #define DTK_ALL_SECS_M     (DTK_M(SECOND) | DTK_M(MILLISECOND) | DTK_M(MICROSECOND))
  #define DTK_DATE_M		(DTK_M(YEAR) | DTK_M(MONTH) | DTK_M(DAY))
  #define DTK_TIME_M		(DTK_M(HOUR) | DTK_M(MINUTE) | DTK_M(SECOND))
  
! #define MAXDATELEN		63		/* maximum possible length of an input date
  								 * string (not counting tr. null) */
  #define MAXDATEFIELDS	25		/* maximum possible number of fields in a date
  								 * string */
*** a/src/interfaces/ecpg/pgtypeslib/interval.c
--- b/src/interfaces/ecpg/pgtypeslib/interval.c
***************
*** 13,51 ****
  #include "pgtypes_error.h"
  #include "pgtypes_interval.h"
  
! /* DecodeInterval()
!  * Interpret previously parsed fields for general time interval.
!  * Return 0 if decoded and -1 if problems.
   *
!  * Allow "date" field DTK_DATE since this could be just
!  *	an unsigned floating point number. - thomas 1997-11-16
   *
!  * Allow ISO-style time span, with implicit units on number of days
!  *	preceding an hh:mm:ss field. - thomas 1998-04-30
   */
  int
! DecodeInterval(char **field, int *ftype, int nf, int *dtype, struct tm * tm, fsec_t *fsec)
  {
! 	int			is_before = FALSE;
! 
  	char	   *cp;
  	int			fmask = 0,
  				tmask,
  				type;
  	int			i;
  	int			val;
  	double		fval;
  
  	*dtype = DTK_DELTA;
- 
  	type = IGNORE_DTF;
! 	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;
  
  	/* read through list backwards to pick up units before values */
  	for (i = nf - 1; i >= 0; i--)
--- 13,359 ----
  #include "pgtypes_error.h"
  #include "pgtypes_interval.h"
  
! /* copy&pasted from .../src/backend/utils/adt/datetime.c */
! static int
! strtoi(const char *nptr, char **endptr, int base)
! {
! 	long	val;
! 
! 	val = strtol(nptr, endptr, base);
! #ifdef HAVE_LONG_INT_64
! 	if (val != (long) ((int32) val))
! 		errno = ERANGE;
! #endif
! 	return (int) val;
! }
! 
! /* copy&pasted from .../src/backend/utils/adt/datetime.c 
!  * and changesd struct pg_tm to struct tm
!  */
! static void
! AdjustFractSeconds(double frac, struct /*pg_*/tm * tm, fsec_t *fsec, int scale)
! {
! 	int	sec;
! 
! 	if (frac == 0)
! 		return;
! 	frac       *= scale;
! 	sec         = (int) frac;
! 	tm->tm_sec += sec;
! 	frac       -= sec;
! #ifdef HAVE_INT64_TIMESTAMP
! 	*fsec      += rint(frac * 1000000);
! #else
! 	*fsec      += frac;
! #endif
! }
! 
! 
! /* copy&pasted from .../src/backend/utils/adt/datetime.c 
!  * and changesd struct pg_tm to struct tm
!  */
! static void
! AdjustFractDays(double frac, struct /*pg_*/tm * tm, fsec_t *fsec, int scale)
! {
! 	int	extra_days;
! 
! 	if (frac == 0)
! 		return;
! 	frac        *= scale;
! 	extra_days   = (int) frac;
! 	tm->tm_mday += extra_days;
! 	frac        -= extra_days;
! 	AdjustFractSeconds(frac, tm, fsec, SECS_PER_DAY);
! }
! 
! /* copy&pasted from .../src/backend/utils/adt/datetime.c */
! static int
! ParseISO8601Number(char *str, char **endptr, int *ipart, double *fpart)
! {
! 	double		val;
! 
! 	if (!(isdigit((unsigned char) *str) || *str == '-' || *str == '.'))
! 		return DTERR_BAD_FORMAT;
! 	errno = 0;
! 	val = strtod(str, endptr);
! 	/* did we not see anything that looks like a double? */
! 	if (*endptr == str || errno != 0)
! 		return DTERR_BAD_FORMAT;
! 	/* watch out for overflow */
! 	if (val < INT_MIN || val > INT_MAX)
! 		return DTERR_FIELD_OVERFLOW;
! 	/* be very sure we truncate towards zero (cf dtrunc()) */
! 	if (val >= 0)
! 		*ipart = (int) floor(val);
! 	else
! 		*ipart = (int) -floor(-val);
! 	*fpart = val - *ipart;
! 	return 0;
! }
! 
! /* copy&pasted from .../src/backend/utils/adt/datetime.c */
! static int
! ISO8601IntegerWidth(char *fieldstart)
! {
! 	/* We might have had a leading '-' */
! 	if (*fieldstart == '-')
! 		fieldstart++;
! 	return strspn(fieldstart, "0123456789");
! }
! 
! 
! /* copy&pasted from .../src/backend/utils/adt/datetime.c 
!  * and changesd struct pg_tm to struct tm
!  */
! static inline void 
! ClearPgTm(struct /*pg_*/tm *tm, fsec_t *fsec)
! {
! 	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;
! }
! 
! /* copy&pasted from .../src/backend/utils/adt/datetime.c 
!  * 
!  * * changesd struct pg_tm to struct tm
!  * 
!  * * Made the function static
!  */
! static int
! DecodeISO8601Interval(char *str,
! 					  int *dtype, struct /*pg_*/tm * tm, fsec_t *fsec)
! {
! 	bool	datepart = true;
! 	bool	havefield = false;
! 
! 	*dtype = DTK_DELTA;
! 	ClearPgTm(tm, fsec);
! 
! 	if (strlen(str) < 2 || str[0] != 'P')
! 		return DTERR_BAD_FORMAT;
! 
! 	str++;
! 	while (*str)
! 	{
! 		char   *fieldstart;
! 		int		val;
! 		double	fval;
! 		char    unit;
! 		int		dterr;
! 
! 		if (*str == 'T') /* T indicates the beginning of the time part */
! 		{
! 			datepart = false;
! 			havefield = false;
! 			str++;
! 			continue;
! 		}
! 
! 		fieldstart = str;
! 		dterr = ParseISO8601Number(str, &str, &val, &fval);
! 		if (dterr)
! 			return dterr;
! 
! 		/*
! 		 * Note: we could step off the end of the string here.  Code below
! 		 * *must* exit the loop if unit == '\0'.
! 		 */
! 		unit = *str++;
! 
! 		if (datepart)
! 		{
! 			switch (unit) /* before T: Y M W D */
! 			{
! 				case 'Y':
! 					tm->tm_year += val;
! 					tm->tm_mon += (fval * 12);
! 					break;
! 				case 'M':
! 					tm->tm_mon += val;
! 					AdjustFractDays(fval, tm, fsec, DAYS_PER_MONTH);
! 					break;
! 				case 'W':
! 					tm->tm_mday += val * 7;
! 					AdjustFractDays(fval, tm, fsec, 7);
! 					break;
! 				case 'D':
! 					tm->tm_mday += val;
! 					AdjustFractSeconds(fval, tm, fsec, SECS_PER_DAY);
! 					break;
! 				case 'T': /* ISO 8601 4.4.3.3 Alternative Format / Basic */
! 				case '\0':
! 					if (ISO8601IntegerWidth(fieldstart) == 8 && !havefield)
! 					{
! 						tm->tm_year += val / 10000;
! 						tm->tm_mon  += (val / 100) % 100;
! 						tm->tm_mday += val % 100;
! 						AdjustFractSeconds(fval, tm, fsec, SECS_PER_DAY);
! 						if (unit == '\0')
! 							return 0;
! 						datepart = false;
! 						havefield = false;
! 						continue;
! 					}
! 					/* Else fall through to extended alternative format */
! 				case '-': /* ISO 8601 4.4.3.3 Alternative Format, Extended */
! 					if (havefield)
! 						return DTERR_BAD_FORMAT;
! 
! 					tm->tm_year += val;
! 					tm->tm_mon  += (fval * 12);
! 					if (unit == '\0')
! 						return 0;
! 					if (unit == 'T')
! 					{
! 						datepart = false;
! 						havefield = false;
! 						continue;
! 					}
! 
! 					dterr = ParseISO8601Number(str, &str, &val, &fval);
! 					if (dterr)
! 						return dterr;
! 					tm->tm_mon  += val;
! 					AdjustFractDays(fval, tm, fsec, DAYS_PER_MONTH);
! 					if (*str == '\0')
! 						return 0;
! 					if (*str == 'T')
! 					{
! 						datepart = false;
! 						havefield = false;
! 						continue;
! 					}
! 					if (*str != '-')
! 						return DTERR_BAD_FORMAT;
! 					str++;
! 					
! 					dterr = ParseISO8601Number(str, &str, &val, &fval);
! 					if (dterr)
! 						return dterr;
! 					tm->tm_mday += val;
! 					AdjustFractSeconds(fval, tm, fsec, SECS_PER_DAY);
! 					if (*str == '\0')
! 						return 0;
! 					if (*str == 'T')
! 					{
! 						datepart = false;
! 						havefield = false;
! 						continue;
! 					}
! 					return DTERR_BAD_FORMAT;
! 				default:
! 					/* not a valid date unit suffix */
! 					return DTERR_BAD_FORMAT;
! 			}
! 		}
! 		else
! 		{
! 			switch (unit) /* after T: H M S */
! 			{
! 				case 'H':
! 					tm->tm_hour += val;
! 					AdjustFractSeconds(fval, tm, fsec, SECS_PER_HOUR);
! 					break;
! 				case 'M':
! 					tm->tm_min += val;
! 					AdjustFractSeconds(fval, tm, fsec, SECS_PER_MINUTE);
! 					break;
! 				case 'S':
! 					tm->tm_sec += val;
! 					AdjustFractSeconds(fval, tm, fsec, 1);
! 					break;
! 				case '\0': /* ISO 8601 4.4.3.3 Alternative Format */
! 				    if (ISO8601IntegerWidth(fieldstart) == 6 && !havefield)
! 					{
! 						tm->tm_hour += val / 10000;
! 						tm->tm_min  += (val / 100) % 100;
! 						tm->tm_sec  += val % 100;
! 						AdjustFractSeconds(fval, tm, fsec, 1);
! 						return 0;
! 					}
! 					/* Else fall through to extended alternative format */
! 				case ':': /* ISO 8601 4.4.3.3 Alternative Format, Extended */
! 					if (havefield)
! 						return DTERR_BAD_FORMAT;
! 
! 					tm->tm_hour += val;
! 					AdjustFractSeconds(fval, tm, fsec, SECS_PER_HOUR);
! 					if (unit == '\0')
! 						return 0;
! 					
! 					dterr = ParseISO8601Number(str, &str, &val, &fval);
! 					if (dterr)
! 						return dterr;
! 					tm->tm_min  += val;
! 					AdjustFractSeconds(fval, tm, fsec, SECS_PER_MINUTE);
! 					if (*str == '\0')
! 						return 0;
! 					if (*str != ':')
! 						return DTERR_BAD_FORMAT;
! 					str++;
! 					
! 					dterr = ParseISO8601Number(str, &str, &val, &fval);
! 					if (dterr)
! 						return dterr;
! 					tm->tm_sec  += val;
! 					AdjustFractSeconds(fval, tm, fsec, 1);
! 					if (*str == '\0')
! 						return 0;
! 					return DTERR_BAD_FORMAT;
! 
! 				default:
! 					/* not a valid time unit suffix */
! 					return DTERR_BAD_FORMAT;
! 			}
! 		}
! 
! 		havefield = true;
! 	}
! 
! 	return 0;
! }
! 
! 
! 
! /* copy&pasted from .../src/backend/utils/adt/datetime.c
!  * with 3 exceptions
!  *
!  *  * changesd struct pg_tm to struct tm
!  *
!  *  * ECPG code called this without a 'range' parameter
!  *    removed 'int range' from the argument list and
!  *    places where DecodeTime is called; and added
!  *       int range = INTERVAL_FULL_RANGE;
   *
!  *  * ECPG semes not to have a global IntervalStyle
!  *    so added
!  *	    int IntervalStyle = INTSTYLE_POSTGRES;
   *
!  *  * Assert wasn't available so removed it.
   */
  int
! DecodeInterval(char **field, int *ftype, int nf, /*int range,*/
! 			   int *dtype, struct /*pg_*/tm * tm, fsec_t *fsec)
  {
!     int IntervalStyle = INTSTYLE_POSTGRES_VERBOSE;
!     int range = INTERVAL_FULL_RANGE;
! 	bool		is_before = FALSE;
  	char	   *cp;
  	int			fmask = 0,
  				tmask,
  				type;
  	int			i;
+ 	int			dterr;
  	int			val;
  	double		fval;
  
  	*dtype = DTK_DELTA;
  	type = IGNORE_DTF;
! 	ClearPgTm(tm,fsec);
  
  	/* read through list backwards to pick up units before values */
  	for (i = nf - 1; i >= 0; i--)
***************
*** 53,60 **** DecodeInterval(char **field, int *ftype, int nf, int *dtype, struct tm * tm, fse
  		switch (ftype[i])
  		{
  			case DTK_TIME:
! 				if (DecodeTime(field[i], fmask, &tmask, tm, fsec) != 0)
! 					return -1;
  				type = DTK_DAY;
  				break;
  
--- 361,370 ----
  		switch (ftype[i])
  		{
  			case DTK_TIME:
! 				dterr = DecodeTime(field[i], fmask, /* range, */
! 								   &tmask, tm, fsec);
! 				if (dterr)
! 					return dterr;
  				type = DTK_DAY;
  				break;
  
***************
*** 62,79 **** DecodeInterval(char **field, int *ftype, int nf, int *dtype, struct tm * tm, fse
  
  				/*
  				 * Timezone is a token with a leading sign character and
! 				 * otherwise the same as a non-signed time field
  				 */
  
  				/*
! 				 * A single signed number ends up here, but will be rejected
! 				 * by DecodeTime(). So, work this out to drop through to
! 				 * DTK_NUMBER, which *can* tolerate this.
  				 */
! 				cp = field[i] + 1;
! 				while (*cp != '\0' && *cp != ':' && *cp != '.')
! 					cp++;
! 				if (*cp == ':' && DecodeTime((field[i] + 1), fmask, &tmask, tm, fsec) == 0)
  				{
  					if (*field[i] == '-')
  					{
--- 372,390 ----
  
  				/*
  				 * Timezone is a token with a leading sign character and
! 				 * at least one digit; there could be ':', '.', '-'
! 				 * embedded in it as well.
  				 */
+ 				/* Assert(*field[i] == '-' || *field[i] == '+'); */
  
  				/*
! 				 * Try for hh:mm or hh:mm:ss.  If not, fall through to
! 				 * DTK_NUMBER case, which can handle signed float numbers
! 				 * and signed year-month values.
  				 */
! 				if (strchr(field[i] + 1, ':') != NULL &&
! 					DecodeTime(field[i] + 1, fmask, /* INTERVAL_FULL_RANGE, */
! 							   &tmask, tm, fsec) == 0)
  				{
  					if (*field[i] == '-')
  					{
***************
*** 93,139 **** DecodeInterval(char **field, int *ftype, int nf, int *dtype, struct tm * tm, fse
  					tmask = DTK_M(TZ);
  					break;
  				}
! 				else if (type == IGNORE_DTF)
  				{
! 					if (*cp == '.')
  					{
! 						/*
! 						 * Got a decimal point? Then assume some sort of
! 						 * seconds specification
! 						 */
! 						type = DTK_SECOND;
! 					}
! 					else if (*cp == '\0')
! 					{
! 						/*
! 						 * Only a signed integer? Then must assume a
! 						 * timezone-like usage
! 						 */
! 						type = DTK_HOUR;
  					}
  				}
- 				/* DROP THROUGH */
  
! 			case DTK_DATE:
! 			case DTK_NUMBER:
! 				val = strtol(field[i], &cp, 10);
  
! 				if (type == IGNORE_DTF)
! 					type = DTK_SECOND;
  
! 				if (*cp == '.')
  				{
  					fval = strtod(cp, &cp);
! 					if (*cp != '\0')
! 						return -1;
  
! 					if (val < 0)
  						fval = -fval;
  				}
  				else if (*cp == '\0')
  					fval = 0;
  				else
! 					return -1;
  
  				tmask = 0;		/* DTK_M(type); */
  
--- 404,484 ----
  					tmask = DTK_M(TZ);
  					break;
  				}
! 				/* FALL THROUGH */
! 
! 			case DTK_DATE:
! 			case DTK_NUMBER:
! 				if (type == IGNORE_DTF)
  				{
! 					/* use typmod to decide what rightmost field is */
! 					switch (range)
  					{
! 						case INTERVAL_MASK(YEAR):
! 							type = DTK_YEAR;
! 							break;
! 						case INTERVAL_MASK(MONTH):
! 						case INTERVAL_MASK(YEAR) | INTERVAL_MASK(MONTH):
! 							type = DTK_MONTH;
! 							break;
! 						case INTERVAL_MASK(DAY):
! 							type = DTK_DAY;
! 							break;
! 						case INTERVAL_MASK(HOUR):
! 						case INTERVAL_MASK(DAY) | INTERVAL_MASK(HOUR):
! 						case INTERVAL_MASK(DAY) | INTERVAL_MASK(HOUR) | INTERVAL_MASK(MINUTE):
! 						case INTERVAL_MASK(DAY) | INTERVAL_MASK(HOUR) | INTERVAL_MASK(MINUTE) | INTERVAL_MASK(SECOND):
! 							type = DTK_HOUR;
! 							break;
! 						case INTERVAL_MASK(MINUTE):
! 						case INTERVAL_MASK(HOUR) | INTERVAL_MASK(MINUTE):
! 							type = DTK_MINUTE;
! 							break;
! 						case INTERVAL_MASK(SECOND):
! 						case INTERVAL_MASK(HOUR) | INTERVAL_MASK(MINUTE) | INTERVAL_MASK(SECOND):
! 						case INTERVAL_MASK(MINUTE) | INTERVAL_MASK(SECOND):
! 							type = DTK_SECOND;
! 							break;
! 						default:
! 							type = DTK_SECOND;
! 							break;
  					}
  				}
  
! 				errno = 0;
! 				val = strtoi(field[i], &cp, 10);
! 				if (errno == ERANGE)
! 					return DTERR_FIELD_OVERFLOW;
  
! 				if (*cp == '-')
! 				{
! 					/* SQL "years-months" syntax */
! 					int		val2;
  
! 					val2 = strtoi(cp + 1, &cp, 10);
! 					if (errno == ERANGE || val2 < 0 || val2 >= MONTHS_PER_YEAR)
! 						return DTERR_FIELD_OVERFLOW;
! 					if (*cp != '\0')
! 						return DTERR_BAD_FORMAT;
! 					type = DTK_MONTH;
! 					if (*field[i] == '-')
! 						val2 = -val2;
! 					val = val * MONTHS_PER_YEAR + val2;
! 					fval = 0;
! 				}
! 				else if (*cp == '.')
  				{
+ 					errno = 0;
  					fval = strtod(cp, &cp);
! 					if (*cp != '\0' || errno != 0)
! 						return DTERR_BAD_FORMAT;
  
! 					if (*field[i] == '-')
  						fval = -fval;
  				}
  				else if (*cp == '\0')
  					fval = 0;
  				else
! 					return DTERR_BAD_FORMAT;
  
  				tmask = 0;		/* DTK_M(type); */
  
***************
*** 141,275 **** DecodeInterval(char **field, int *ftype, int nf, int *dtype, struct tm * tm, fse
  				{
  					case DTK_MICROSEC:
  #ifdef HAVE_INT64_TIMESTAMP
! 						*fsec += val + fval;
  #else
  						*fsec += (val + fval) * 1e-6;
  #endif
  						break;
  
  					case DTK_MILLISEC:
  #ifdef HAVE_INT64_TIMESTAMP
! 						*fsec += (val + fval) * 1000;
  #else
  						*fsec += (val + fval) * 1e-3;
  #endif
  						break;
  
  					case DTK_SECOND:
  						tm->tm_sec += val;
  #ifdef HAVE_INT64_TIMESTAMP
! 						*fsec += fval * 1000000;
  #else
  						*fsec += fval;
  #endif
! 						tmask = DTK_M(SECOND);
  						break;
  
  					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 += ((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 += (fval - sec) * 1000000;
! #else
! 							*fsec += fval - sec;
! #endif
! 						}
  						tmask = DTK_M(HOUR);
  						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 += (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 += (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 += (fval - sec) * 1000000;
! #else
! 								*fsec += fval - sec;
! #endif
! 							}
! 						}
  						tmask = DTK_M(MONTH);
  						break;
  
--- 486,553 ----
  				{
  					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.
! 						 */
! 						if (fval == 0)
! 							tmask = DTK_M(SECOND);
! 						else
! 							tmask = DTK_ALL_SECS_M;
  						break;
  
  					case DTK_MINUTE:
  						tm->tm_min += val;
! 						AdjustFractSeconds(fval, tm, fsec, SECS_PER_MINUTE);
  						tmask = DTK_M(MINUTE);
  						break;
  
  					case DTK_HOUR:
  						tm->tm_hour += val;
! 						AdjustFractSeconds(fval, tm, fsec, SECS_PER_HOUR);
  						tmask = DTK_M(HOUR);
+ 						type = DTK_DAY;
  						break;
  
  					case DTK_DAY:
  						tm->tm_mday += val;
! 						AdjustFractSeconds(fval, tm, fsec, SECS_PER_DAY);
  						tmask = (fmask & DTK_M(DAY)) ? 0 : DTK_M(DAY);
  						break;
  
  					case DTK_WEEK:
  						tm->tm_mday += val * 7;
! 						AdjustFractDays(fval, tm, fsec, 7);
  						tmask = (fmask & DTK_M(DAY)) ? 0 : DTK_M(DAY);
  						break;
  
  					case DTK_MONTH:
  						tm->tm_mon += val;
! 						AdjustFractDays(fval, tm, fsec, DAYS_PER_MONTH);
  						tmask = DTK_M(MONTH);
  						break;
  
***************
*** 302,308 **** DecodeInterval(char **field, int *ftype, int nf, int *dtype, struct tm * tm, fse
  						break;
  
  					default:
! 						return -1;
  				}
  				break;
  
--- 580,586 ----
  						break;
  
  					default:
! 						return DTERR_BAD_FORMAT;
  				}
  				break;
  
***************
*** 330,348 **** DecodeInterval(char **field, int *ftype, int nf, int *dtype, struct tm * tm, fse
  						break;
  
  					default:
! 						return -1;
  				}
  				break;
  
  			default:
! 				return -1;
  		}
  
  		if (tmask & fmask)
! 			return -1;
  		fmask |= tmask;
  	}
  
  	if (*fsec != 0)
  	{
  		int			sec;
--- 608,631 ----
  						break;
  
  					default:
! 						return DTERR_BAD_FORMAT;
  				}
  				break;
  
  			default:
! 				return DTERR_BAD_FORMAT;
  		}
  
  		if (tmask & fmask)
! 			return DTERR_BAD_FORMAT;
  		fmask |= tmask;
  	}
  
+ 	/* ensure that at least one time field has been found */
+ 	if (fmask == 0)
+ 		return DTERR_BAD_FORMAT;
+ 
+ 	/* ensure fractional seconds are fractional */
  	if (*fsec != 0)
  	{
  		int			sec;
***************
*** 356,605 **** DecodeInterval(char **field, int *ftype, int nf, int *dtype, struct tm * tm, fse
  		tm->tm_sec += sec;
  	}
  
  	if (is_before)
  	{
  		*fsec = -(*fsec);
! 		tm->tm_sec = -(tm->tm_sec);
! 		tm->tm_min = -(tm->tm_min);
! 		tm->tm_hour = -(tm->tm_hour);
! 		tm->tm_mday = -(tm->tm_mday);
! 		tm->tm_mon = -(tm->tm_mon);
! 		tm->tm_year = -(tm->tm_year);
  	}
  
! 	/* ensure that at least one time field has been found */
! 	return (fmask != 0) ? 0 : -1;
! }	/* DecodeInterval() */
  
! /* EncodeInterval()
!  * Interpret time structure as a delta time and convert to string.
!  *
!  * Support "traditional Postgres" and ISO-8601 styles.
!  * Actually, afaik ISO does not address time interval formatting,
!  *	but this looks similar to the spec for absolute date/time.
!  * - thomas 1998-04-30
   */
  int
! EncodeInterval(struct tm * tm, fsec_t fsec, int style, char *str)
  {
! 	int			is_before = FALSE;
! 	int			is_nonzero = FALSE;
  	char	   *cp = str;
  
  	/*
  	 * 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)
  	{
! 			/* compatible with ISO date formats */
! 		case USE_ISO_DATES:
! 			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;
  
! 				/* 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);
! 					is_nonzero = TRUE;
  				}
! 				/* otherwise, integer seconds only? */
! 				else if (tm->tm_sec != 0)
  				{
! 					sprintf(cp, ":%02d", abs(tm->tm_sec));
  					cp += strlen(cp);
! 					is_nonzero = TRUE;
  				}
  			}
  			break;
  
! 		case USE_POSTGRES_DATES:
! 		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)
! 			{
! #ifdef HAVE_INT64_TIMESTAMP
! 				if (is_before || (!is_nonzero && tm->tm_sec < 0))
! 					tm->tm_sec = -tm->tm_sec;
! 				sprintf(cp, "%s%d.%02d secs", is_nonzero ? " " : "",
! 						tm->tm_sec, ((int) fsec) / 10000);
! 				cp += strlen(cp);
! 				if (!is_nonzero)
! 					is_before = (fsec < 0);
! #else
! 				fsec_t		sec;
! 
! 				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;
  			}
  			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() */
  
  /* interval2tm()
   * Convert a interval data type to a tm structure.
   */
--- 639,982 ----
  		tm->tm_sec += sec;
  	}
  
+ 	/*----------
+ 	 * The SQL standard defines the interval literal
+ 	 *   '-1 1:00:00'
+ 	 * to mean "negative 1 days and negative 1 hours", while Postgres
+ 	 * traditionally treats this as meaning "negative 1 days and positive
+ 	 * 1 hours".  In SQL_STANDARD intervalstyle, we apply the leading sign
+ 	 * to all fields if there are no other explicit signs.
+ 	 *
+ 	 * We leave the signs alone if there are additional explicit signs.
+ 	 * This protects us against misinterpreting postgres-style dump output,
+ 	 * since the postgres-style output code has always put an explicit sign on
+ 	 * all fields following a negative field.  But note that SQL-spec output
+ 	 * is ambiguous and can be misinterpreted on load!  (So it's best practice
+ 	 * to dump in postgres style, not SQL style.)
+ 	 *----------
+ 	 */
+ 	if (IntervalStyle == INTSTYLE_SQL_STANDARD && *field[0] == '-')
+ 	{
+ 		/* Check for additional explicit signs */
+ 		bool	more_signs = false;
+ 
+ 		for (i = 1; i < nf; i++)
+ 		{
+ 			if (*field[i] == '-' || *field[i] == '+')
+ 			{
+ 				more_signs = true;
+ 				break;
+ 			}
+ 		}
+ 
+ 		if (!more_signs)
+ 		{
+ 			/*
+ 			 * Rather than re-determining which field was field[0], just
+ 			 * force 'em all negative.
+ 			 */
+ 			if (*fsec > 0)
+ 				*fsec = -(*fsec);
+ 			if (tm->tm_sec > 0)
+ 				tm->tm_sec = -tm->tm_sec;
+ 			if (tm->tm_min > 0)
+ 				tm->tm_min = -tm->tm_min;
+ 			if (tm->tm_hour > 0)
+ 				tm->tm_hour = -tm->tm_hour;
+ 			if (tm->tm_mday > 0)
+ 				tm->tm_mday = -tm->tm_mday;
+ 			if (tm->tm_mon > 0)
+ 				tm->tm_mon = -tm->tm_mon;
+ 			if (tm->tm_year > 0)
+ 				tm->tm_year = -tm->tm_year;
+ 		}
+ 	}
+ 
+ 	/* finally, AGO negates everything */
  	if (is_before)
  	{
  		*fsec = -(*fsec);
! 		tm->tm_sec = -tm->tm_sec;
! 		tm->tm_min = -tm->tm_min;
! 		tm->tm_hour = -tm->tm_hour;
! 		tm->tm_mday = -tm->tm_mday;
! 		tm->tm_mon = -tm->tm_mon;
! 		tm->tm_year = -tm->tm_year;
  	}
  
! 	return 0;
! }
  
! 
! /* copy&pasted from .../src/backend/utils/adt/datetime.c */
! static char *
! AddVerboseIntPart(char *cp, int value, const char *units,
! 				  bool *is_zero, bool *is_before)
! {
! 	if (value == 0)
! 		return cp;
! 	/* first nonzero value sets is_before */
! 	if (*is_zero)
! 	{
! 		*is_before = (value < 0);
! 		value = abs(value);
! 	}
! 	else if (*is_before)
! 		value = -value;
! 	sprintf(cp, " %d %s%s", value, units, (value == 1) ? "" : "s");
! 	*is_zero = FALSE;
! 	return cp + strlen(cp);
! }
! 
! /* copy&pasted from .../src/backend/utils/adt/datetime.c */
! static char *
! AddPostgresIntPart(char *cp, int value, const char *units,
! 				   bool *is_zero, bool *is_before)
! {
! 	if (value == 0)
! 		return cp;
! 	sprintf(cp, "%s%s%d %s%s",
! 			(!*is_zero) ? " " : "",
! 			(*is_before && value > 0) ? "+" : "",
! 			value,
! 			units,
! 			(value != 1) ? "s" : "");
! 	/*
! 	 * Each nonzero field sets is_before for (only) the next one.  This is
! 	 * a tad bizarre but it's how it worked before...
! 	 */
! 	*is_before = (value < 0);
! 	*is_zero = FALSE;
! 	return cp + strlen(cp);
! }
! 
! /* copy&pasted from .../src/backend/utils/adt/datetime.c */
! static char *
! AddISO8601IntPart(char *cp, int value, char units)
! {
! 	if (value == 0)
! 		return cp;
! 	sprintf(cp, "%d%c", value, units);
! 	return cp + strlen(cp);
! }
! 
! /* copy&pasted from .../src/backend/utils/adt/datetime.c */
! static void
! AppendSeconds(char *cp, int sec, fsec_t fsec, int precision, bool fillzeros)
! {
! 	if (fsec == 0)
! 	{
! 		if (fillzeros)
! 			sprintf(cp, "%02d", abs(sec));
! 		else
! 			sprintf(cp, "%d", abs(sec));
! 	}
! 	else
! 	{
! #ifdef HAVE_INT64_TIMESTAMP
! 		if (fillzeros)
! 			sprintf(cp, "%02d.%0*d", abs(sec), precision, (int) Abs(fsec));
! 		else
! 			sprintf(cp, "%d.%0*d", abs(sec), precision, (int) Abs(fsec));
! #else
! 		if (fillzeros)
! 			sprintf(cp, "%0*.*f", precision + 3, precision, fabs(sec + fsec));
! 		else
! 			sprintf(cp, "%.*f", precision, fabs(sec + fsec));
! #endif
! 		TrimTrailingZeros(cp);
! 	}
! }
! 
! 
! /* copy&pasted from .../src/backend/utils/adt/datetime.c
!  * 
!  * Change pg_tm to tm
   */
+ 
  int
! EncodeInterval(struct /*pg_*/tm * tm, fsec_t fsec, int style, char *str)
  {
! 
  	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;
+ 	bool		is_before = FALSE;
+ 	bool		is_zero = TRUE;
  
  	/*
  	 * 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_zero when determining the signs of day and hour/minute/seconds
! 	 * fields.
  	 */
  	switch (style)
  	{
! 		/* SQL Standard interval format */
! 		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_day_time   = mday != 0 || hour != 0 ||
! 									  min  != 0 || sec  != 0 || fsec != 0;
! 				bool has_day        = mday != 0;
! 				bool sql_standard_value = !(has_negative && has_positive) &&
! 										  !(has_year_month && has_day_time);
  
! 				/*
! 				 * SQL Standard wants only 1 "<sign>" preceding the whole
! 				 * interval ... but can't do that if mixed signs.
! 				 */
! 				if (has_negative && sql_standard_value)
! 				{
! 					*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) ? '-' : '+';
! 
! 					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));
  					cp += strlen(cp);
! 					AppendSeconds(cp, sec, fsec, MAX_INTERVAL_PRECISION, true);
! 				}
! 				else if (has_year_month)
! 				{
! 					sprintf(cp, "%d-%d", year, mon);
! 				}
! 				else if (has_day)
! 				{
! 					sprintf(cp, "%d %d:%02d:", mday, hour, min);
  					cp += strlen(cp);
! 					AppendSeconds(cp, sec, fsec, MAX_INTERVAL_PRECISION, true);
  				}
! 				else
  				{
! 					sprintf(cp, "%d:%02d:", hour, min);
  					cp += strlen(cp);
! 					AppendSeconds(cp, sec, fsec, MAX_INTERVAL_PRECISION, true);
  				}
  			}
  			break;
  
! 		/* ISO 8601 "time-intervals by duration only" */
! 		case INTSTYLE_ISO_8601:
! 			/* special-case zero to avoid printing nothing */
! 			if (year == 0 && mon == 0 && mday == 0 &&
! 			    hour == 0 && min == 0 && sec  == 0 && fsec == 0)
  			{
! 				sprintf(cp, "PT0S");
! 				break;
  			}
! 			*cp++ = 'P';
! 			cp = AddISO8601IntPart(cp, year, 'Y');
! 			cp = AddISO8601IntPart(cp, mon , 'M');
! 			cp = AddISO8601IntPart(cp, mday, 'D');
! 			if (hour != 0 || min != 0 || sec != 0 || fsec != 0)
! 				*cp++ = 'T';
! 			cp = AddISO8601IntPart(cp, hour, 'H');
! 			cp = AddISO8601IntPart(cp, min , 'M');
! 			if (sec != 0 || fsec != 0)
  			{
! 				if (sec < 0 || fsec < 0)
! 					*cp++ = '-';
! 				AppendSeconds(cp, sec, fsec, MAX_INTERVAL_PRECISION, false);
  				cp += strlen(cp);
! 				*cp++ = 'S';
! 				*cp++ = '\0';
  			}
+ 			break;
  
! 		/* Compatible with postgresql < 8.4 when DateStyle = 'iso' */
! 		case INTSTYLE_POSTGRES:
! 			cp = AddPostgresIntPart(cp, year, "year", &is_zero, &is_before);
! 			cp = AddPostgresIntPart(cp, mon, "mon", &is_zero, &is_before);
! 			cp = AddPostgresIntPart(cp, mday, "day", &is_zero, &is_before);
! 			if (is_zero || hour != 0 || min != 0 || sec != 0 || fsec != 0)
  			{
! 				bool	minus = (hour < 0 || min < 0 || sec < 0 || fsec < 0);
  
! 				sprintf(cp, "%s%s%02d:%02d:",
! 						is_zero ? "" : " ",
! 						(minus ? "-" : (is_before ? "+" : "")),
! 						abs(hour), abs(min));
  				cp += strlen(cp);
! 				AppendSeconds(cp, sec, fsec, MAX_INTERVAL_PRECISION, true);
  			}
+ 			break;
  
! 		/* Compatible with postgresql < 8.4 when DateStyle != 'iso' */
! 		case INTSTYLE_POSTGRES_VERBOSE:
! 		default:
! 			strcpy(cp, "@");
! 			cp++;
! 			cp = AddVerboseIntPart(cp, year, "year", &is_zero, &is_before);
! 			cp = AddVerboseIntPart(cp, mon, "mon", &is_zero, &is_before);
! 			cp = AddVerboseIntPart(cp, mday, "day", &is_zero, &is_before);
! 			cp = AddVerboseIntPart(cp, hour, "hour", &is_zero, &is_before);
! 			cp = AddVerboseIntPart(cp, min, "min", &is_zero, &is_before);
! 			if (sec != 0 || fsec != 0)
  			{
! 				*cp++ = ' ';
! 				if (sec < 0 || (sec == 0 && fsec < 0))
! 				{
! 					if (is_zero)
! 						is_before = TRUE;
! 					else if (!is_before)
! 						*cp++ = '-';
! 				}
! 				else if (is_before)
! 					*cp++ = '-';
! 				AppendSeconds(cp, sec, fsec, MAX_INTERVAL_PRECISION, false);
  				cp += strlen(cp);
! 				sprintf(cp, " sec%s",
! 						(abs(sec) != 1 || fsec != 0) ? "s" : "");
! 				is_zero = FALSE;
  			}
+ 			/* identically zero? then put in a unitless zero... */
+ 			if (is_zero)
+ 				strcat(cp, " 0");
+ 			if (is_before)
+ 				strcat(cp, " ago");
  			break;
  	}
  
  	return 0;
  }	/* EncodeInterval() */
  
+ 
  /* interval2tm()
   * Convert a interval data type to a tm structure.
   */
***************
*** 719,725 **** PGTYPESinterval_from_asc(char *str, char **endptr)
  	}
  
  	if (ParseDateTime(str, lowstr, field, ftype, MAXDATEFIELDS, &nf, ptr) != 0 ||
! 		DecodeInterval(field, ftype, nf, &dtype, tm, &fsec) != 0)
  	{
  		errno = PGTYPES_INTVL_BAD_INTERVAL;
  		return NULL;
--- 1096,1103 ----
  	}
  
  	if (ParseDateTime(str, lowstr, field, ftype, MAXDATEFIELDS, &nf, ptr) != 0 ||
! 		(DecodeInterval(field, ftype, nf, &dtype, tm, &fsec) != 0 &&
! 		 DecodeISO8601Interval(str, &dtype, tm, &fsec) != 0))
  	{
  		errno = PGTYPES_INTVL_BAD_INTERVAL;
  		return NULL;
***************
*** 754,760 **** PGTYPESinterval_to_asc(interval * span)
  			   *tm = &tt;
  	fsec_t		fsec;
  	char		buf[MAXDATELEN + 1];
! 	int			DateStyle = 0;
  
  	if (interval2tm(*span, tm, &fsec) != 0)
  	{
--- 1132,1138 ----
  			   *tm = &tt;
  	fsec_t		fsec;
  	char		buf[MAXDATELEN + 1];
! 	int		    IntervalStyle = INTSTYLE_POSTGRES_VERBOSE;
  
  	if (interval2tm(*span, tm, &fsec) != 0)
  	{
***************
*** 762,768 **** PGTYPESinterval_to_asc(interval * span)
  		return NULL;
  	}
  
! 	if (EncodeInterval(tm, fsec, DateStyle, buf) != 0)
  	{
  		errno = PGTYPES_INTVL_BAD_INTERVAL;
  		return NULL;
--- 1140,1146 ----
  		return NULL;
  	}
  
! 	if (EncodeInterval(tm, fsec, IntervalStyle, buf) != 0)
  	{
  		errno = PGTYPES_INTVL_BAD_INTERVAL;
  		return NULL;
*** a/src/interfaces/ecpg/test/expected/pgtypeslib-dt_test.c
--- b/src/interfaces/ecpg/test/expected/pgtypeslib-dt_test.c
***************
*** 77,82 **** if (sqlca.sqlcode < 0) sqlprint (  );}
--- 77,88 ----
  if (sqlca.sqlcode < 0) sqlprint (  );}
  #line 30 "dt_test.pgc"
  
+ 	{ ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "set intervalstyle to postgres_verbose", ECPGt_EOIT, ECPGt_EORT);
+ #line 31 "dt_test.pgc"
+ 
+ if (sqlca.sqlcode < 0) sqlprint (  );}
+ #line 31 "dt_test.pgc"
+ 
  
  	date1 = PGTYPESdate_from_asc(d1, NULL); 
  	ts1 = PGTYPEStimestamp_from_asc(t1, NULL); 
***************
*** 86,95 **** if (sqlca.sqlcode < 0) sqlprint (  );}
  	ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, 
  	ECPGt_timestamp,&(ts1),(long)1,(long)1,sizeof(timestamp), 
  	ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EOIT, ECPGt_EORT);
! #line 35 "dt_test.pgc"
  
  if (sqlca.sqlcode < 0) sqlprint (  );}
! #line 35 "dt_test.pgc"
  
  
  	{ ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select  *  from date_test where d =  $1   ", 
--- 92,101 ----
  	ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, 
  	ECPGt_timestamp,&(ts1),(long)1,(long)1,sizeof(timestamp), 
  	ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EOIT, ECPGt_EORT);
! #line 36 "dt_test.pgc"
  
  if (sqlca.sqlcode < 0) sqlprint (  );}
! #line 36 "dt_test.pgc"
  
  
  	{ ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select  *  from date_test where d =  $1   ", 
***************
*** 99,108 **** if (sqlca.sqlcode < 0) sqlprint (  );}
  	ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, 
  	ECPGt_timestamp,&(ts1),(long)1,(long)1,sizeof(timestamp), 
  	ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT);
! #line 37 "dt_test.pgc"
  
  if (sqlca.sqlcode < 0) sqlprint (  );}
! #line 37 "dt_test.pgc"
  
  
  	text = PGTYPESdate_to_asc(date1);
--- 105,114 ----
  	ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, 
  	ECPGt_timestamp,&(ts1),(long)1,(long)1,sizeof(timestamp), 
  	ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT);
! #line 38 "dt_test.pgc"
  
  if (sqlca.sqlcode < 0) sqlprint (  );}
! #line 38 "dt_test.pgc"
  
  
  	text = PGTYPESdate_to_asc(date1);
***************
*** 417,432 **** if (sqlca.sqlcode < 0) sqlprint (  );}
  	free(text);
  
  	{ ECPGtrans(__LINE__, NULL, "rollback ");
! #line 350 "dt_test.pgc"
  
  if (sqlca.sqlcode < 0) sqlprint (  );}
! #line 350 "dt_test.pgc"
  
          { ECPGdisconnect(__LINE__, "CURRENT");
! #line 351 "dt_test.pgc"
  
  if (sqlca.sqlcode < 0) sqlprint (  );}
! #line 351 "dt_test.pgc"
  
  
  	return (0);
--- 423,438 ----
  	free(text);
  
  	{ ECPGtrans(__LINE__, NULL, "rollback ");
! #line 351 "dt_test.pgc"
  
  if (sqlca.sqlcode < 0) sqlprint (  );}
! #line 351 "dt_test.pgc"
  
          { ECPGdisconnect(__LINE__, "CURRENT");
! #line 352 "dt_test.pgc"
  
  if (sqlca.sqlcode < 0) sqlprint (  );}
! #line 352 "dt_test.pgc"
  
  
  	return (0);
*** a/src/interfaces/ecpg/test/expected/pgtypeslib-dt_test.stderr
--- b/src/interfaces/ecpg/test/expected/pgtypeslib-dt_test.stderr
***************
*** 14,42 ****
  [NO_PID]: sqlca: code: 0, state: 00000
  [NO_PID]: ecpg_execute on line 30: OK: SET
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 35: query: insert into date_test ( d  , ts  ) values (  $1  ,  $2  ) ; with 2 parameter(s) on connection regress1
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 35: using PQexecParams
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: free_params on line 35: parameter 1 = 1966-01-17
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: free_params on line 35: parameter 2 = 2000-07-12 17:34:29
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 35: OK: INSERT 0 1
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 37: query: select  *  from date_test where d =  $1   ; with 1 parameter(s) on connection regress1
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 37: using PQexecParams
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: free_params on line 37: parameter 1 = 1966-01-17
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 37: correctly got 1 tuples with 2 fields
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_get_data on line 37: RESULT: 1966-01-17 offset: -1; array: yes
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_get_data on line 37: RESULT: 2000-07-12 17:34:29 offset: -1; array: yes
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ECPGtrans on line 350: action "rollback "; connection "regress1"
  [NO_PID]: sqlca: code: 0, state: 00000
  [NO_PID]: ecpg_finish: connection regress1 closed
  [NO_PID]: sqlca: code: 0, state: 00000
--- 14,48 ----
  [NO_PID]: sqlca: code: 0, state: 00000
  [NO_PID]: ecpg_execute on line 30: OK: SET
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 31: query: set intervalstyle to postgres_verbose; with 0 parameter(s) on connection regress1
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 31: using PQexec
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 31: OK: SET
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 36: query: insert into date_test ( d  , ts  ) values (  $1  ,  $2  ) ; with 2 parameter(s) on connection regress1
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 36: using PQexecParams
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: free_params on line 36: parameter 1 = 1966-01-17
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: free_params on line 36: parameter 2 = 2000-07-12 17:34:29
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 36: OK: INSERT 0 1
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 38: query: select  *  from date_test where d =  $1   ; with 1 parameter(s) on connection regress1
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 38: using PQexecParams
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: free_params on line 38: parameter 1 = 1966-01-17
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 38: correctly got 1 tuples with 2 fields
! [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_get_data on line 38: RESULT: 1966-01-17 offset: -1; array: yes
! [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_get_data on line 38: RESULT: 2000-07-12 17:34:29 offset: -1; array: yes
! [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ECPGtrans on line 351: action "rollback "; connection "regress1"
  [NO_PID]: sqlca: code: 0, state: 00000
  [NO_PID]: ecpg_finish: connection regress1 closed
  [NO_PID]: sqlca: code: 0, state: 00000
*** a/src/interfaces/ecpg/test/pgtypeslib/dt_test.pgc
--- b/src/interfaces/ecpg/test/pgtypeslib/dt_test.pgc
***************
*** 28,33 **** main(void)
--- 28,34 ----
          exec sql connect to REGRESSDB1;
          exec sql create table date_test (d date, ts timestamp);
  	exec sql set datestyle to iso;
+ 	exec sql set intervalstyle to postgres_verbose;
  
  	date1 = PGTYPESdate_from_asc(d1, NULL); 
  	ts1 = PGTYPEStimestamp_from_asc(t1, NULL);