Add support for input and output of interval values formatted per ISO 8601;

specifically, we can input either the "format with designators" or the
"alternative format", and we can output the former when IntervalStyle is set
to iso_8601.

Ron Mayer

9 files changed, 474 insertions, 28 deletions

diff --git a/src/backend/utils/adt/datetime.c b/src/backend/utils/adt/datetime.c
index e91c470304f..ef61b3eb560 100644
--- a/src/backend/utils/adt/datetime.c
+++ b/src/backend/utils/adt/datetime.c
@@ -8,7 +8,7 @@
  *
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/utils/adt/datetime.c,v 1.197 2008/11/09 00:28:34 tgl Exp $
+ *	  $PostgreSQL: pgsql/src/backend/utils/adt/datetime.c,v 1.198 2008/11/11 02:42:32 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -2726,6 +2726,7 @@ DecodeSpecial(int field, char *lowtoken, int *val)
 /* DecodeInterval()
  * Interpret previously parsed fields for general time interval.
  * Returns 0 if successful, DTERR code if bogus input detected.
+ * dtype, tm, fsec are output parameters.
  *
  * Allow "date" field DTK_DATE since this could be just
  *	an unsigned floating point number. - thomas 1997-11-16
@@ -3188,6 +3189,307 @@ DecodeInterval(char **field, int *ftype, int nf, int range,
 }
 
 
+/*
+ * Helper functions to avoid duplicated code in DecodeISO8601Interval.
+ *
+ * Parse a decimal value and break it into integer and fractional parts.
+ * Returns 0 or DTERR code.
+ */
+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;
+}
+
+/*
+ * Determine number of integral digits in a valid ISO 8601 number field
+ * (we should ignore sign and any fraction part)
+ */
+static int
+ISO8601IntegerWidth(char *fieldstart)
+{
+	/* We might have had a leading '-' */
+	if (*fieldstart == '-')
+		fieldstart++;
+	return strspn(fieldstart, "0123456789");
+}
+
+/*
+ * Multiply frac by scale (to produce seconds) and add to *tm & *fsec.
+ * We assume the input frac is less than 1 so overflow is not an issue.
+ */
+static void
+AdjustFractionalSeconds(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
+}
+
+/* As above, but initial scale produces days */
+static void
+AdjustFractionalDays(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;
+	AdjustFractionalSeconds(frac, tm, fsec, SECS_PER_DAY);
+}
+
+
+/* DecodeISO8601Interval()
+ *  Decode an ISO 8601 time interval of the "format with designators"
+ *  (section 4.4.3.2) or "alternative format" (section 4.4.3.3)
+ *  Examples:  P1D  for 1 day
+ *             PT1H for 1 hour
+ *             P2Y6M7DT1H30M for 2 years, 6 months, 7 days 1 hour 30 min
+ *             P0002-06-07T01:30:00 the same value in alternative format
+ *
+ * Returns 0 if successful, DTERR code if bogus input detected.
+ * Note: error code should be DTERR_BAD_FORMAT if input doesn't look like
+ * ISO8601, otherwise this could cause unexpected error messages.
+ * dtype, tm, fsec are output parameters.
+ *
+ *  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,
+					  int *dtype, struct pg_tm * tm, fsec_t *fsec)
+{
+	bool	datepart = true;
+	bool	havefield = false;
+
+	*dtype = DTK_DELTA;
+
+	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) < 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;
+					AdjustFractionalDays(fval, tm, fsec, DAYS_PER_MONTH);
+					break;
+				case 'W':
+					tm->tm_mday += val * 7;
+					AdjustFractionalDays(fval, tm, fsec, 7);
+					break;
+				case 'D':
+					tm->tm_mday += val;
+					AdjustFractionalSeconds(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;
+						AdjustFractionalSeconds(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;
+					AdjustFractionalDays(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;
+					AdjustFractionalSeconds(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;
+					AdjustFractionalSeconds(fval, tm, fsec, SECS_PER_HOUR);
+					break;
+				case 'M':
+					tm->tm_min += val;
+					AdjustFractionalSeconds(fval, tm, fsec, SECS_PER_MINUTE);
+					break;
+				case 'S':
+					tm->tm_sec += val;
+					AdjustFractionalSeconds(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;
+						AdjustFractionalSeconds(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;
+					AdjustFractionalSeconds(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;
+					AdjustFractionalSeconds(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;
+					AdjustFractionalSeconds(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;
+}
+
+
 /* DecodeUnits()
  * Decode text string using lookup table.
  * This routine supports time interval decoding
@@ -3662,27 +3964,39 @@ EncodeDateTime(struct pg_tm * tm, fsec_t fsec, int *tzp, char **tzn, int style,
 
 
 /*
- * Helper function to avoid duplicated code in EncodeInterval below.
+ * Helper functions to avoid duplicated code in EncodeInterval.
+ *
+ * Append sections and fractional seconds (if any) at *cp.
  * Note that any sign is stripped from the input seconds values.
  */
 static void
-AppendSeconds(char *cp, int sec, fsec_t fsec)
+AppendSeconds(char *cp, int sec, fsec_t fsec, bool fillzeros)
 {
 	if (fsec == 0)
 	{
-		sprintf(cp, ":%02d", abs(sec));
+		sprintf(cp, fillzeros ? "%02d" : "%d", abs(sec));
 	}
 	else
 	{
 #ifdef HAVE_INT64_TIMESTAMP
-		sprintf(cp, ":%02d.%06d", abs(sec), Abs(fsec));
+		sprintf(cp, fillzeros ? "%02d.%06d" : "%d.%06d", abs(sec), Abs(fsec));
 #else
-		sprintf(cp, ":%012.9f", fabs(sec + fsec));
+		sprintf(cp, fillzeros ? "%012.9f" : "%.9f", fabs(sec + fsec));
 #endif
 		TrimTrailingZeros(cp);
 	}
 }
 
+/* Append an ISO8601 field, but only if value isn't zero */
+static char *
+AddISO8601IntervalPart(char *cp, int value, char units)
+{
+	if (value == 0)
+		return cp;
+	sprintf(cp, "%d%c", value, units);
+	return cp + strlen(cp);
+}
+
 
 /* EncodeInterval()
  * Interpret time structure as a delta time and convert to string.
@@ -3772,12 +4086,12 @@ EncodeInterval(struct pg_tm * tm, fsec_t fsec, int style, char *str)
 					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",
+					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);
+					AppendSeconds(cp, sec, fsec, true);
 				}
 				else if (has_year_month)
 				{
@@ -3785,19 +4099,47 @@ EncodeInterval(struct pg_tm * tm, fsec_t fsec, int style, char *str)
 				}
 				else if (has_day)
 				{
-					sprintf(cp, "%d %d:%02d", mday, hour, min);
+					sprintf(cp, "%d %d:%02d:", mday, hour, min);
 					cp += strlen(cp);
-					AppendSeconds(cp, sec, fsec);
+					AppendSeconds(cp, sec, fsec, true);
 				}
 				else
 				{
-					sprintf(cp, "%d:%02d", hour, min);
+					sprintf(cp, "%d:%02d:", hour, min);
 					cp += strlen(cp);
-					AppendSeconds(cp, sec, fsec);
+					AppendSeconds(cp, sec, fsec, 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 = AddISO8601IntervalPart(cp, year, 'Y');
+			cp = AddISO8601IntervalPart(cp, mon , 'M');
+			cp = AddISO8601IntervalPart(cp, mday, 'D');
+			if (hour != 0 || min != 0 || sec != 0 || fsec != 0)
+				*cp++ = 'T';
+			cp = AddISO8601IntervalPart(cp, hour, 'H');
+			cp = AddISO8601IntervalPart(cp, min , 'M');
+			if (sec != 0 || fsec != 0)
+			{
+				if (sec < 0 || fsec < 0)
+					*cp++ = '-';
+				AppendSeconds(cp, sec, fsec, false);
+				cp += strlen(cp);
+				*cp++ = 'S';
+				*cp++ = '\0';
+			}
+			break;
+
 		/* Compatible with postgresql < 8.4 when DateStyle = 'iso' */
 		case INTSTYLE_POSTGRES:
 			if (tm->tm_year != 0)
@@ -3835,11 +4177,11 @@ EncodeInterval(struct pg_tm * tm, fsec_t fsec, int style, char *str)
 				int			minus = (tm->tm_hour < 0 || tm->tm_min < 0 ||
 									 tm->tm_sec < 0 || fsec < 0);
 
-				sprintf(cp, "%s%s%02d:%02d", is_nonzero ? " " : "",
+				sprintf(cp, "%s%s%02d:%02d:", is_nonzero ? " " : "",
 						(minus ? "-" : (is_before ? "+" : "")),
 						abs(tm->tm_hour), abs(tm->tm_min));
 				cp += strlen(cp);
-				AppendSeconds(cp, tm->tm_sec, fsec);
+				AppendSeconds(cp, tm->tm_sec, fsec, true);
 				cp += strlen(cp);
 				is_nonzero = TRUE;
 			}
diff --git a/src/backend/utils/adt/nabstime.c b/src/backend/utils/adt/nabstime.c
index 6744818e412..0dd8ab5e5a6 100644
--- a/src/backend/utils/adt/nabstime.c
+++ b/src/backend/utils/adt/nabstime.c
@@ -10,7 +10,7 @@
  *
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/utils/adt/nabstime.c,v 1.157 2008/11/09 00:28:35 tgl Exp $
+ *	  $PostgreSQL: pgsql/src/backend/utils/adt/nabstime.c,v 1.158 2008/11/11 02:42:32 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -634,6 +634,12 @@ reltimein(PG_FUNCTION_ARGS)
 	if (dterr == 0)
 		dterr = DecodeInterval(field, ftype, nf, INTERVAL_FULL_RANGE,
 							   &dtype, tm, &fsec);
+
+	/* if those functions think it's a bad format, try ISO8601 style */
+	if (dterr == DTERR_BAD_FORMAT)
+	    dterr = DecodeISO8601Interval(str,
+									  &dtype, tm, &fsec);
+
 	if (dterr != 0)
 	{
 		if (dterr == DTERR_FIELD_OVERFLOW)
diff --git a/src/backend/utils/adt/timestamp.c b/src/backend/utils/adt/timestamp.c
index ce633c7a4fd..d0d9afc9586 100644
--- a/src/backend/utils/adt/timestamp.c
+++ b/src/backend/utils/adt/timestamp.c
@@ -8,7 +8,7 @@
  *
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/utils/adt/timestamp.c,v 1.194 2008/11/09 00:28:35 tgl Exp $
+ *	  $PostgreSQL: pgsql/src/backend/utils/adt/timestamp.c,v 1.195 2008/11/11 02:42:32 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -626,7 +626,14 @@ interval_in(PG_FUNCTION_ARGS)
 	dterr = ParseDateTime(str, workbuf, sizeof(workbuf), field,
 						  ftype, MAXDATEFIELDS, &nf);
 	if (dterr == 0)
-		dterr = DecodeInterval(field, ftype, nf, range, &dtype, tm, &fsec);
+		dterr = DecodeInterval(field, ftype, nf, range,
+							   &dtype, tm, &fsec);
+
+	/* if those functions think it's a bad format, try ISO8601 style */
+	if (dterr == DTERR_BAD_FORMAT)
+	    dterr = DecodeISO8601Interval(str,
+									  &dtype, tm, &fsec);
+
 	if (dterr != 0)
 	{
 		if (dterr == DTERR_FIELD_OVERFLOW)
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index 6a5faa725da..143003f3844 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -10,7 +10,7 @@
  * Written by Peter Eisentraut <peter_e@gmx.net>.
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/utils/misc/guc.c,v 1.476 2008/11/09 00:28:35 tgl Exp $
+ *	  $PostgreSQL: pgsql/src/backend/utils/misc/guc.c,v 1.477 2008/11/11 02:42:32 tgl Exp $
  *
  *--------------------------------------------------------------------
  */
@@ -217,6 +217,7 @@ static const struct config_enum_entry intervalstyle_options[] = {
 	{"postgres", INTSTYLE_POSTGRES, false},
 	{"postgres_verbose", INTSTYLE_POSTGRES_VERBOSE, false},
 	{"sql_standard", INTSTYLE_SQL_STANDARD, false},
+	{"iso_8601", INTSTYLE_ISO_8601, false},
 	{NULL, 0, false}
 };
 
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 8c38aaf95bd..d262f21771e 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -3,7 +3,7 @@
  *
  * Copyright (c) 2000-2008, PostgreSQL Global Development Group
  *
- * $PostgreSQL: pgsql/src/bin/psql/tab-complete.c,v 1.175 2008/11/09 00:28:35 tgl Exp $
+ * $PostgreSQL: pgsql/src/bin/psql/tab-complete.c,v 1.176 2008/11/11 02:42:32 tgl Exp $
  */
 
 /*----------------------------------------------------------------------
@@ -1959,7 +1959,7 @@ 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};
+			{"postgres", "postgres_verbose", "sql_standard", "iso_8601", NULL};
 
 			COMPLETE_WITH_LIST(my_list);
 		}
diff --git a/src/include/miscadmin.h b/src/include/miscadmin.h
index 9348a527aa6..3a3f3830991 100644
--- a/src/include/miscadmin.h
+++ b/src/include/miscadmin.h
@@ -13,7 +13,7 @@
  * Portions Copyright (c) 1996-2008, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $PostgreSQL: pgsql/src/include/miscadmin.h,v 1.204 2008/11/09 00:28:35 tgl Exp $
+ * $PostgreSQL: pgsql/src/include/miscadmin.h,v 1.205 2008/11/11 02:42:32 tgl Exp $
  *
  * NOTES
  *	  some of the information in this file should be moved to other files.
@@ -197,10 +197,12 @@ extern int	DateOrder;
  *   INTSTYLE_POSTGRES             Like Postgres < 8.4 when DateStyle = 'iso'
  *   INTSTYLE_POSTGRES_VERBOSE     Like Postgres < 8.4 when DateStyle != 'iso'
  *   INTSTYLE_SQL_STANDARD         SQL standard interval literals
+ *   INTSTYLE_ISO_8601             ISO-8601-basic formatted intervals
  */
-#define INTSTYLE_POSTGRES             0
-#define INTSTYLE_POSTGRES_VERBOSE     1
-#define INTSTYLE_SQL_STANDARD         2
+#define INTSTYLE_POSTGRES			0
+#define INTSTYLE_POSTGRES_VERBOSE	1
+#define INTSTYLE_SQL_STANDARD		2
+#define INTSTYLE_ISO_8601			3
 
 extern int	IntervalStyle;
 
diff --git a/src/include/utils/datetime.h b/src/include/utils/datetime.h
index 439e9779d20..9f5d979bcf1 100644
--- a/src/include/utils/datetime.h
+++ b/src/include/utils/datetime.h
@@ -9,7 +9,7 @@
  * Portions Copyright (c) 1996-2008, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $PostgreSQL: pgsql/src/include/utils/datetime.h,v 1.70 2008/09/10 18:29:41 tgl Exp $
+ * $PostgreSQL: pgsql/src/include/utils/datetime.h,v 1.71 2008/11/11 02:42:32 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -289,9 +289,11 @@ extern int DecodeDateTime(char **field, int *ftype,
 extern int DecodeTimeOnly(char **field, int *ftype,
 			   int nf, int *dtype,
 			   struct pg_tm * tm, fsec_t *fsec, int *tzp);
-extern int DecodeInterval(char **field, int *ftype,
-			   int nf, int range, int *dtype,
-			   struct pg_tm * tm, fsec_t *fsec);
+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,
+			   int *dtype, struct pg_tm * tm, fsec_t *fsec);
+
 extern void DateTimeParseError(int dterr, const char *str,
 				   const char *datatype);
 
diff --git a/src/test/regress/expected/interval.out b/src/test/regress/expected/interval.out
index e8fee7a38e9..94a4275404a 100644
--- a/src/test/regress/expected/interval.out
+++ b/src/test/regress/expected/interval.out
@@ -646,3 +646,54 @@ SELECT  interval '1 day -1 hours',
  +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)
 
+-- 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 ISO 8601 4.4.2.1 "Format With Time Unit Designators"
+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)
+
+-- test inputting ISO 8601 4.4.2.2 "Alternative Format"
+SET IntervalStyle to postgres;
+select  interval 'P00021015T103020'       AS "ISO8601 Basic Format",
+        interval 'P0002-10-15T10:30:20'   AS "ISO8601 Extended Format";
+       ISO8601 Basic Format       |     ISO8601 Extended Format      
+----------------------------------+----------------------------------
+ 2 years 10 mons 15 days 10:30:20 | 2 years 10 mons 15 days 10:30:20
+(1 row)
+
+-- Make sure optional ISO8601 alternative format fields are optional.
+select  interval 'P0002'                  AS "year only",
+        interval 'P0002-10'               AS "year month",
+        interval 'P0002-10-15'            AS "year month day",
+        interval 'P0002T1S'               AS "year only plus time",
+        interval 'P0002-10T1S'            AS "year month plus time",
+        interval 'P0002-10-15T1S'         AS "year month day plus time",
+        interval 'PT10'                   AS "hour only",
+        interval 'PT10:30'                AS "hour minute";
+ year only |   year month    |     year month day      | year only plus time |   year month plus time   |     year month day plus time     | hour only | hour minute 
+-----------+-----------------+-------------------------+---------------------+--------------------------+----------------------------------+-----------+-------------
+ 2 years   | 2 years 10 mons | 2 years 10 mons 15 days | 2 years 00:00:01    | 2 years 10 mons 00:00:01 | 2 years 10 mons 15 days 00:00:01 | 10:00:00  | 10:30:00
+(1 row)
+
diff --git a/src/test/regress/sql/interval.sql b/src/test/regress/sql/interval.sql
index 9b32dd6f3b3..ce9560b0b09 100644
--- a/src/test/regress/sql/interval.sql
+++ b/src/test/regress/sql/interval.sql
@@ -200,3 +200,38 @@ 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';
+
+-- 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 ISO 8601 4.4.2.1 "Format With Time Unit Designators"
+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";
+
+-- test inputting ISO 8601 4.4.2.2 "Alternative Format"
+SET IntervalStyle to postgres;
+select  interval 'P00021015T103020'       AS "ISO8601 Basic Format",
+        interval 'P0002-10-15T10:30:20'   AS "ISO8601 Extended Format";
+
+-- Make sure optional ISO8601 alternative format fields are optional.
+select  interval 'P0002'                  AS "year only",
+        interval 'P0002-10'               AS "year month",
+        interval 'P0002-10-15'            AS "year month day",
+        interval 'P0002T1S'               AS "year only plus time",
+        interval 'P0002-10T1S'            AS "year month plus time",
+        interval 'P0002-10-15T1S'         AS "year month day plus time",
+        interval 'PT10'                   AS "hour only",
+        interval 'PT10:30'                AS "hour minute";