Fix up some problems in handling of zic-style time zone names in datetime

input routines.  Remove the former "DecodePosixTimezone" function in favor of
letting the zic code handle POSIX-style zone specs (see tzparse()).  In
particular this means that "PST+3" now means the same as "-03", whereas it
used to mean "-11" --- the zone abbreviation is effectively just a noise word
in this syntax.  Make sure that all named and POSIX-style zone names will be
parsed as a single token.  Fix long-standing bogosities in printing and input
of fractional-hour timezone offsets (since the tzparse() code will accept
these, we'd better make 'em work).  Also correct an error in the original
coding of the zic-zone-name patch: in "timestamp without time zone" input,
zone names are supposed to be allowed but ignored, but the coding was such
that the zone changed the interpretation anyway.

6 files changed, 195 insertions, 273 deletions

diff --git a/src/backend/utils/adt/datetime.c b/src/backend/utils/adt/datetime.c
index fac642ddc97..cfb29dce568 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.172 2006/10/04 00:29:58 momjian Exp $
+ *	  $PostgreSQL: pgsql/src/backend/utils/adt/datetime.c,v 1.173 2006/10/17 21:03:21 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -39,8 +39,6 @@ static int DecodeNumberField(int len, char *str,
 static int DecodeTime(char *str, int fmask, int *tmask,
 		   struct pg_tm * tm, fsec_t *fsec);
 static int	DecodeTimezone(char *str, int *tzp);
-static int	DecodePosixTimezone(char *str, int *tzp);
-static int	DecodeZicTimezone(char *str, int *tzp, struct pg_tm * tm);
 static const datetkn *datebsearch(const char *key, const datetkn *base, int nel);
 static int	DecodeDate(char *str, int fmask, int *tmask, struct pg_tm * tm);
 static void TrimTrailingZeros(char *str);
@@ -173,7 +171,7 @@ static const datetkn datetktbl[] = {
 	{"wednesday", DOW, 3},
 	{"weds", DOW, 3},
 	{"y", UNITS, DTK_YEAR},		/* "year" for ISO input */
-	{YESTERDAY, RESERV, DTK_YESTERDAY}, /* yesterday midnight */
+	{YESTERDAY, RESERV, DTK_YESTERDAY}	/* yesterday midnight */
 };
 
 static int	szdatetktbl = sizeof datetktbl / sizeof datetktbl[0];
@@ -243,7 +241,7 @@ static datetkn deltatktbl[] = {
 	{DYEAR, UNITS, DTK_YEAR},	/* "year" relative */
 	{"years", UNITS, DTK_YEAR}, /* "years" relative */
 	{"yr", UNITS, DTK_YEAR},	/* "year" relative */
-	{"yrs", UNITS, DTK_YEAR},	/* "years" relative */
+	{"yrs", UNITS, DTK_YEAR}	/* "years" relative */
 };
 
 static int	szdeltatktbl = sizeof deltatktbl / sizeof deltatktbl[0];
@@ -427,14 +425,14 @@ TrimTrailingZeros(char *str)
  *	DTK_NUMBER - digits and (possibly) a decimal point
  *	DTK_DATE - digits and two delimiters, or digits and text
  *	DTK_TIME - digits, colon delimiters, and possibly a decimal point
- *	DTK_STRING - text (no digits)
+ *	DTK_STRING - text (no digits or punctuation)
  *	DTK_SPECIAL - leading "+" or "-" followed by text
- *	DTK_TZ - leading "+" or "-" followed by digits
+ *	DTK_TZ - leading "+" or "-" followed by digits (also eats ':' or '.')
  *
  * Note that some field types can hold unexpected items:
  *	DTK_NUMBER can hold date fields (yy.ddd)
  *	DTK_STRING can hold months (January) and time zones (PST)
- *	DTK_DATE can hold Posix time zones (GMT-8)
+ *	DTK_DATE can hold time zone names (America/New_York, GMT-8)
  */
 int
 ParseDateTime(const char *timestr, char *workbuf, size_t buflen,
@@ -546,46 +544,42 @@ ParseDateTime(const char *timestr, char *workbuf, size_t buflen,
 		 */
 		else if (isalpha((unsigned char) *cp))
 		{
+			bool	is_date;
+
 			ftype[nf] = DTK_STRING;
 			APPEND_CHAR(bufp, bufend, pg_tolower((unsigned char) *cp++));
 			while (isalpha((unsigned char) *cp))
 				APPEND_CHAR(bufp, bufend, pg_tolower((unsigned char) *cp++));
 
 			/*
-			 * Full date string with leading text month? Could also be a POSIX
-			 * time zone...
+			 * Dates can have embedded '-', '/', or '.' separators.  It could
+			 * also be a timezone name containing embedded '/', '+', '-',
+			 * '_', or ':' (but '_' or ':' can't be the first punctuation).
+			 * If the next character is a digit or '+', we need to check
+			 * whether what we have so far is a recognized non-timezone
+			 * keyword --- if so, don't believe that this is the start of
+			 * a timezone.
 			 */
+			is_date = false;
 			if (*cp == '-' || *cp == '/' || *cp == '.')
+				is_date = true;
+			else if (*cp == '+' || isdigit((unsigned char) *cp))
 			{
-				char		delim = *cp;
-
-				if (*cp == '/')
-				{
-					ftype[nf] = DTK_TZ;
-
-					/*
-					 * set the first character of the region to upper case
-					 * again
-					 */
-					field[nf][0] = pg_toupper((unsigned char) field[nf][0]);
-
-					/*
-					 * we have seen "Region/" of a POSIX timezone, continue to
-					 * read the City part
-					 */
-					do
-					{
-						APPEND_CHAR(bufp, bufend, *cp++);
-						/* there is for example America/New_York */
-					} while (isalpha((unsigned char) *cp) || *cp == '_');
-				}
-				else
+				*bufp = '\0';	/* null-terminate current field value */
+				/* we need search only the core token table, not TZ names */
+				if (datebsearch(field[nf], datetktbl, szdatetktbl) == NULL)
+					is_date = true;
+			}
+			if (is_date)
+			{
+				ftype[nf] = DTK_DATE;
+				do
 				{
-					ftype[nf] = DTK_DATE;
-					APPEND_CHAR(bufp, bufend, *cp++);
-				}
-				while (isdigit((unsigned char) *cp) || *cp == delim)
-					APPEND_CHAR(bufp, bufend, *cp++);
+					APPEND_CHAR(bufp, bufend, pg_tolower((unsigned char) *cp++));
+				} while (*cp == '+' || *cp == '-' ||
+						 *cp == '/' || *cp == '_' ||
+						 *cp == '.' || *cp == ':' ||
+						 isalnum((unsigned char) *cp));
 			}
 		}
 		/* sign? then special or numeric timezone */
@@ -674,7 +668,7 @@ DecodeDateTime(char **field, int *ftype, int nf,
 	bool		haveTextMonth = FALSE;
 	int			is2digits = FALSE;
 	int			bc = FALSE;
-	int			zicTzFnum = -1;
+	pg_tz	   *namedTz = NULL;
 
 	/*
 	 * We'll insist on at least all of the date fields, but initialize the
@@ -724,8 +718,8 @@ DecodeDateTime(char **field, int *ftype, int nf,
 					break;
 				}
 				/***
-				 * Already have a date? Then this might be a POSIX time
-				 * zone with an embedded dash (e.g. "PST-3" == "EST") or
+				 * Already have a date? Then this might be a time zone name
+				 * with embedded punctuation (e.g. "America/New_York") or
 				 * a run-together time with trailing time zone (e.g. hhmmss-zz).
 				 * - thomas 2001-12-25
 				 ***/
@@ -774,7 +768,6 @@ DecodeDateTime(char **field, int *ftype, int nf,
 												  fsec, &is2digits);
 						if (dterr < 0)
 							return dterr;
-						ftype[i] = dterr;
 
 						/*
 						 * modify tmask after returning from
@@ -784,11 +777,20 @@ DecodeDateTime(char **field, int *ftype, int nf,
 					}
 					else
 					{
-						dterr = DecodePosixTimezone(field[i], tzp);
-						if (dterr)
-							return dterr;
-
-						ftype[i] = DTK_TZ;
+						namedTz = pg_tzset(field[i]);
+						if (!namedTz)
+						{
+							/*
+							 * We should return an error code instead of
+							 * ereport'ing directly, but then there is no
+							 * way to report the bad time zone name.
+							 */
+							ereport(ERROR,
+									(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+									 errmsg("time zone \"%s\" not recognized",
+											field[i])));
+						}
+						/* we'll apply the zone setting below */
 						tmask = DTK_M(TZ);
 					}
 				}
@@ -822,34 +824,11 @@ DecodeDateTime(char **field, int *ftype, int nf,
 					if (tzp == NULL)
 						return DTERR_BAD_FORMAT;
 
-					if (strchr(field[i], '/') != NULL)
-					{
-						/* remember to apply the timezone at the end */
-						zicTzFnum = i;
-						tmask = DTK_M(TZ);
-						break;
-					}
-					else
-						dterr = DecodeTimezone(field[i], &tz);
+					dterr = DecodeTimezone(field[i], &tz);
 					if (dterr)
 						return dterr;
-
-					/*
-					 * Already have a time zone? Then maybe this is the second
-					 * field of a POSIX time: EST+3 (equivalent to PST)
-					 */
-					if (i > 0 && (fmask & DTK_M(TZ)) != 0 &&
-						ftype[i - 1] == DTK_TZ &&
-						isalpha((unsigned char) *field[i - 1]))
-					{
-						*tzp -= tz;
-						tmask = 0;
-					}
-					else
-					{
-						*tzp = tz;
-						tmask = DTK_M(TZ);
-					}
+					*tzp = tz;
+					tmask = DTK_M(TZ);
 				}
 				break;
 
@@ -988,8 +967,6 @@ DecodeDateTime(char **field, int *ftype, int nf,
 													  fsec, &is2digits);
 							if (dterr < 0)
 								return dterr;
-							ftype[i] = dterr;
-
 							if (tmask != DTK_TIME_M)
 								return DTERR_BAD_FORMAT;
 							break;
@@ -1030,7 +1007,6 @@ DecodeDateTime(char **field, int *ftype, int nf,
 												  fsec, &is2digits);
 						if (dterr < 0)
 							return dterr;
-						ftype[i] = dterr;
 					}
 					else if (flen > 4)
 					{
@@ -1039,7 +1015,6 @@ DecodeDateTime(char **field, int *ftype, int nf,
 												  fsec, &is2digits);
 						if (dterr < 0)
 							return dterr;
-						ftype[i] = dterr;
 					}
 					/* otherwise it is a single date/time field... */
 					else
@@ -1168,7 +1143,6 @@ DecodeDateTime(char **field, int *ftype, int nf,
 						if (tzp == NULL)
 							return DTERR_BAD_FORMAT;
 						*tzp = val * MINS_PER_HOUR;
-						ftype[i] = DTK_TZ;
 						break;
 
 					case TZ:
@@ -1176,7 +1150,6 @@ DecodeDateTime(char **field, int *ftype, int nf,
 						if (tzp == NULL)
 							return DTERR_BAD_FORMAT;
 						*tzp = val * MINS_PER_HOUR;
-						ftype[i] = DTK_TZ;
 						break;
 
 					case IGNORE_DTF:
@@ -1308,18 +1281,17 @@ DecodeDateTime(char **field, int *ftype, int nf,
 		if (tm->tm_mday > day_tab[isleap(tm->tm_year)][tm->tm_mon - 1])
 			return DTERR_FIELD_OVERFLOW;
 
-		if (zicTzFnum != -1)
+		/*
+		 * If we had a full timezone spec, compute the offset (we could not
+		 * do it before, because we need the date to resolve DST status).
+		 */
+		if (namedTz != NULL)
 		{
-			Datum		tsTz;
-			Timestamp	timestamp;
-
-			tm2timestamp(tm, *fsec, NULL, &timestamp);
-			tsTz = DirectFunctionCall2(timestamp_zone,
-									   DirectFunctionCall1(textin,
-										  CStringGetDatum(field[zicTzFnum])),
-									   TimestampGetDatum(timestamp));
-			timestamp2tm(DatumGetTimestampTz(tsTz), tzp, tm, fsec, NULL, NULL);
-			fmask &= ~DTK_M(TZ);
+			/* daylight savings time modifier disallowed with full TZ */
+			if (fmask & DTK_M(DTZMOD))
+				return DTERR_BAD_FORMAT;
+
+			*tzp = DetermineTimeZoneOffset(tm, namedTz);
 		}
 
 		/* timezone not specified? then find local timezone if possible */
@@ -1492,6 +1464,7 @@ DecodeTimeOnly(char **field, int *ftype, int nf,
 	int			dterr;
 	int			is2digits = FALSE;
 	int			mer = HR24;
+	pg_tz	   *namedTz = NULL;
 
 	*dtype = DTK_TIME;
 	tm->tm_hour = 0;
@@ -1567,10 +1540,20 @@ DecodeTimeOnly(char **field, int *ftype, int nf,
 					}
 					else
 					{
-						dterr = DecodePosixTimezone(field[i], tzp);
-						if (dterr)
-							return dterr;
-
+						namedTz = pg_tzset(field[i]);
+						if (!namedTz)
+						{
+							/*
+							 * We should return an error code instead of
+							 * ereport'ing directly, but then there is no
+							 * way to report the bad time zone name.
+							 */
+							ereport(ERROR,
+									(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+									 errmsg("time zone \"%s\" not recognized",
+											field[i])));
+						}
+						/* we'll apply the zone setting below */
 						ftype[i] = DTK_TZ;
 						tmask = DTK_M(TZ);
 					}
@@ -1591,34 +1574,11 @@ DecodeTimeOnly(char **field, int *ftype, int nf,
 					if (tzp == NULL)
 						return DTERR_BAD_FORMAT;
 
-					if (strchr(field[i], '/') != NULL)
-					{
-						/* a date has to be specified */
-						if ((fmask & DTK_DATE_M) != DTK_DATE_M)
-							return DTERR_BAD_FORMAT;
-						dterr = DecodeZicTimezone(field[i], &tz, tm);
-					}
-					else
-						dterr = DecodeTimezone(field[i], &tz);
+					dterr = DecodeTimezone(field[i], &tz);
 					if (dterr)
 						return dterr;
-
-					/*
-					 * Already have a time zone? Then maybe this is the second
-					 * field of a POSIX time: EST+3 (equivalent to PST)
-					 */
-					if (i > 0 && (fmask & DTK_M(TZ)) != 0 &&
-						ftype[i - 1] == DTK_TZ &&
-						isalpha((unsigned char) *field[i - 1]))
-					{
-						*tzp -= tz;
-						tmask = 0;
-					}
-					else
-					{
-						*tzp = tz;
-						tmask = DTK_M(TZ);
-					}
+					*tzp = tz;
+					tmask = DTK_M(TZ);
 				}
 				break;
 
@@ -1974,21 +1934,38 @@ DecodeTimeOnly(char **field, int *ftype, int nf,
 
 	if (tm->tm_hour < 0 || tm->tm_min < 0 || tm->tm_min > 59 ||
 		tm->tm_sec < 0 || tm->tm_sec > 60 || tm->tm_hour > 24 ||
-	/* test for > 24:00:00 */
-		(tm->tm_hour == 24 && (tm->tm_min > 0 || tm->tm_sec > 0 ||
+		/* test for > 24:00:00 */
 #ifdef HAVE_INT64_TIMESTAMP
+		(tm->tm_hour == 24 && (tm->tm_min > 0 || tm->tm_sec > 0 ||
 							   *fsec > INT64CONST(0))) ||
-		*fsec < INT64CONST(0) || *fsec >= USECS_PER_SEC)
-		return DTERR_FIELD_OVERFLOW;
+		*fsec < INT64CONST(0) || *fsec >= USECS_PER_SEC
 #else
+		(tm->tm_hour == 24 && (tm->tm_min > 0 || tm->tm_sec > 0 ||
 							   *fsec > 0)) ||
-		*fsec < 0 || *fsec >= 1)
-		return DTERR_FIELD_OVERFLOW;
+		*fsec < 0 || *fsec >= 1
 #endif
+		)
+		return DTERR_FIELD_OVERFLOW;
 
 	if ((fmask & DTK_TIME_M) != DTK_TIME_M)
 		return DTERR_BAD_FORMAT;
 
+	/*
+	 * If we had a full timezone spec, compute the offset (we could not
+	 * do it before, because we need the date to resolve DST status).
+	 */
+	if (namedTz != NULL)
+	{
+		/* a date has to be specified */
+		if ((fmask & DTK_DATE_M) != DTK_DATE_M)
+			return DTERR_BAD_FORMAT;
+		/* daylight savings time modifier disallowed with full TZ */
+		if (fmask & DTK_M(DTZMOD))
+			return DTERR_BAD_FORMAT;
+
+		*tzp = DetermineTimeZoneOffset(tm, namedTz);
+	}
+
 	/* timezone not specified? then find local timezone if possible */
 	if (tzp != NULL && !(fmask & DTK_M(TZ)))
 	{
@@ -2548,7 +2525,8 @@ DecodeTimezone(char *str, int *tzp)
 {
 	int			tz;
 	int			hr,
-				min;
+				min,
+				sec = 0;
 	char	   *cp;
 
 	/* leading character must be "+" or "-" */
@@ -2567,22 +2545,32 @@ DecodeTimezone(char *str, int *tzp)
 		min = strtol(cp + 1, &cp, 10);
 		if (errno == ERANGE)
 			return DTERR_TZDISP_OVERFLOW;
+		if (*cp == ':')
+		{
+			errno = 0;
+			sec = strtol(cp + 1, &cp, 10);
+			if (errno == ERANGE)
+				return DTERR_TZDISP_OVERFLOW;
+		}
 	}
 	/* otherwise, might have run things together... */
 	else if (*cp == '\0' && strlen(str) > 3)
 	{
 		min = hr % 100;
 		hr = hr / 100;
+		/* we could, but don't, support a run-together hhmmss format */
 	}
 	else
 		min = 0;
 
-	if (hr < 0 || hr > 13)
+	if (hr < 0 || hr > 14)
 		return DTERR_TZDISP_OVERFLOW;
 	if (min < 0 || min >= 60)
 		return DTERR_TZDISP_OVERFLOW;
+	if (sec < 0 || sec >= 60)
+		return DTERR_TZDISP_OVERFLOW;
 
-	tz = (hr * MINS_PER_HOUR + min) * SECS_PER_MINUTE;
+	tz = (hr * MINS_PER_HOUR + min) * SECS_PER_MINUTE + sec;
 	if (*str == '-')
 		tz = -tz;
 
@@ -2594,75 +2582,6 @@ DecodeTimezone(char *str, int *tzp)
 	return 0;
 }
 
-
-/* DecodePosixTimezone()
- * Interpret string as a POSIX-compatible timezone:
- *	PST-hh:mm
- *	PST+h
- *	PST
- * - thomas 2000-03-15
- *
- * Return 0 if okay (and set *tzp), a DTERR code if not okay.
- */
-static int
-DecodePosixTimezone(char *str, int *tzp)
-{
-	int			val,
-				tz;
-	int			type;
-	int			dterr;
-	char	   *cp;
-	char		delim;
-
-	/* advance over name part */
-	cp = str;
-	while (*cp && isalpha((unsigned char) *cp))
-		cp++;
-
-	/* decode offset, if present */
-	if (*cp)
-	{
-		dterr = DecodeTimezone(cp, &tz);
-		if (dterr)
-			return dterr;
-	}
-	else
-		tz = 0;
-
-	/* decode name part.  We must temporarily scribble on the input! */
-	delim = *cp;
-	*cp = '\0';
-	type = DecodeSpecial(MAXDATEFIELDS - 1, str, &val);
-	*cp = delim;
-
-	switch (type)
-	{
-		case DTZ:
-		case TZ:
-			*tzp = (val * MINS_PER_HOUR) - tz;
-			break;
-
-		default:
-			return DTERR_BAD_FORMAT;
-	}
-
-	return 0;
-}
-
-static int
-DecodeZicTimezone(char *str, int *tzp, struct pg_tm * tm)
-{
-	struct pg_tz *tz;
-
-	tz = pg_tzset(str);
-	if (!tz)
-		return DTERR_BAD_FORMAT;
-
-	*tzp = DetermineTimeZoneOffset(tm, tz);
-
-	return 0;
-}
-
 /* DecodeSpecial()
  * Decode text string using lookup table.
  *
@@ -3194,6 +3113,33 @@ datebsearch(const char *key, const datetkn *base, int nel)
 	return NULL;
 }
 
+/* EncodeTimezone()
+ *		Append representation of a numeric timezone offset to str.
+ */
+static void
+EncodeTimezone(char *str, int tz)
+{
+	int			hour,
+				min,
+				sec;
+
+	sec = abs(tz);
+	min = sec / SECS_PER_MINUTE;
+	sec -= min * SECS_PER_MINUTE;
+	hour = min / MINS_PER_HOUR;
+	min -= hour * MINS_PER_HOUR;
+
+	str += strlen(str);
+	/* TZ is negated compared to sign we wish to display ... */
+	*str++ = (tz <= 0 ? '+' : '-');
+
+	if (sec != 0)
+		sprintf(str, "%02d:%02d:%02d", hour, min, sec);
+	else if (min != 0)
+		sprintf(str, "%02d:%02d", hour, min);
+	else
+		sprintf(str, "%02d", hour);
+}
 
 /* EncodeDateOnly()
  * Encode date as local time.
@@ -3284,14 +3230,7 @@ EncodeTimeOnly(struct pg_tm * tm, fsec_t fsec, int *tzp, int style, char *str)
 		sprintf(str + strlen(str), ":%02d", tm->tm_sec);
 
 	if (tzp != NULL)
-	{
-		int			hour,
-					min;
-
-		hour = -(*tzp / SECS_PER_HOUR);
-		min = (abs(*tzp) / MINS_PER_HOUR) % MINS_PER_HOUR;
-		sprintf(str + strlen(str), (min != 0) ? "%+03d:%02d" : "%+03d", hour, min);
-	}
+		EncodeTimezone(str, *tzp);
 
 	return TRUE;
 }	/* EncodeTimeOnly() */
@@ -3311,9 +3250,7 @@ EncodeTimeOnly(struct pg_tm * tm, fsec_t fsec, int *tzp, int style, char *str)
 int
 EncodeDateTime(struct pg_tm * tm, fsec_t fsec, int *tzp, char **tzn, int style, char *str)
 {
-	int			day,
-				hour,
-				min;
+	int			day;
 
 	/*
 	 * Why are we checking only the month field? Change this to an assert...
@@ -3360,11 +3297,7 @@ EncodeDateTime(struct pg_tm * tm, fsec_t fsec, int *tzp, char **tzn, int style,
 			 * a valid time zone translation.
 			 */
 			if (tzp != NULL && tm->tm_isdst >= 0)
-			{
-				hour = -(*tzp / SECS_PER_HOUR);
-				min = (abs(*tzp) / MINS_PER_HOUR) % MINS_PER_HOUR;
-				sprintf(str + strlen(str), (min != 0) ? "%+03d:%02d" : "%+03d", hour, min);
-			}
+				EncodeTimezone(str, *tzp);
 
 			if (tm->tm_year <= 0)
 				sprintf(str + strlen(str), " BC");
@@ -3410,11 +3343,7 @@ EncodeDateTime(struct pg_tm * tm, fsec_t fsec, int *tzp, char **tzn, int style,
 				if (*tzn != NULL)
 					sprintf(str + strlen(str), " %.*s", MAXTZLEN, *tzn);
 				else
-				{
-					hour = -(*tzp / SECS_PER_HOUR);
-					min = (abs(*tzp) / MINS_PER_HOUR) % MINS_PER_HOUR;
-					sprintf(str + strlen(str), (min != 0) ? "%+03d:%02d" : "%+03d", hour, min);
-				}
+					EncodeTimezone(str, *tzp);
 			}
 
 			if (tm->tm_year <= 0)
@@ -3458,11 +3387,7 @@ EncodeDateTime(struct pg_tm * tm, fsec_t fsec, int *tzp, char **tzn, int style,
 				if (*tzn != NULL)
 					sprintf(str + strlen(str), " %.*s", MAXTZLEN, *tzn);
 				else
-				{
-					hour = -(*tzp / SECS_PER_HOUR);
-					min = (abs(*tzp) / MINS_PER_HOUR) % MINS_PER_HOUR;
-					sprintf(str + strlen(str), (min != 0) ? "%+03d:%02d" : "%+03d", hour, min);
-				}
+					EncodeTimezone(str, *tzp);
 			}
 
 			if (tm->tm_year <= 0)
@@ -3524,9 +3449,8 @@ EncodeDateTime(struct pg_tm * tm, fsec_t fsec, int *tzp, char **tzn, int style,
 					 * avoid formatting something which would be rejected by
 					 * the date/time parser later. - thomas 2001-10-19
 					 */
-					hour = -(*tzp / SECS_PER_HOUR);
-					min = (abs(*tzp) / MINS_PER_HOUR) % MINS_PER_HOUR;
-					sprintf(str + strlen(str), (min != 0) ? " %+03d:%02d" : " %+03d", hour, min);
+					sprintf(str + strlen(str), " ");
+					EncodeTimezone(str, *tzp);
 				}
 			}
 
diff --git a/src/test/regress/expected/horology.out b/src/test/regress/expected/horology.out
index 15b397c74f2..3aa8e3714bf 100644
--- a/src/test/regress/expected/horology.out
+++ b/src/test/regress/expected/horology.out
@@ -448,7 +448,7 @@ SELECT '' AS "64", d1 + interval '1 year' AS one_year FROM TIMESTAMP_TBL;
     | Tue Feb 10 17:32:01 1998
     | Tue Feb 10 17:32:01 1998
     | Tue Feb 10 17:32:01 1998
-    | Tue Feb 10 14:32:01 1998
+    | Tue Feb 10 17:32:01 1998
     | Wed Jun 10 18:32:01 1998
     | Tue Feb 10 17:32:01 1998
     | Wed Feb 11 17:32:01 1998
@@ -518,7 +518,7 @@ SELECT '' AS "64", d1 - interval '1 year' AS one_year FROM TIMESTAMP_TBL;
     | Sat Feb 10 17:32:01 1996
     | Sat Feb 10 17:32:01 1996
     | Sat Feb 10 17:32:01 1996
-    | Sat Feb 10 14:32:01 1996
+    | Sat Feb 10 17:32:01 1996
     | Mon Jun 10 18:32:01 1996
     | Sat Feb 10 17:32:01 1996
     | Sun Feb 11 17:32:01 1996
@@ -936,8 +936,8 @@ SELECT t.d1 + i.f1 AS "102" FROM TIMESTAMP_TBL t, INTERVAL_TBL i
  Mon Feb 10 22:32:01 1997
  Mon Feb 10 17:33:01 1997
  Mon Feb 10 22:32:01 1997
- Mon Feb 10 14:33:01 1997
- Mon Feb 10 19:32:01 1997
+ Mon Feb 10 17:33:01 1997
+ Mon Feb 10 22:32:01 1997
  Tue Jun 10 18:33:01 1997
  Tue Jun 10 23:32:01 1997
  Mon Feb 10 17:33:01 1997
@@ -1047,8 +1047,8 @@ SELECT t.d1 - i.f1 AS "102" FROM TIMESTAMP_TBL t, INTERVAL_TBL i
  Mon Feb 10 12:32:01 1997
  Mon Feb 10 17:31:01 1997
  Mon Feb 10 12:32:01 1997
- Mon Feb 10 14:31:01 1997
- Mon Feb 10 09:32:01 1997
+ Mon Feb 10 17:31:01 1997
+ Mon Feb 10 12:32:01 1997
  Tue Jun 10 18:31:01 1997
  Tue Jun 10 13:32:01 1997
  Mon Feb 10 17:31:01 1997
@@ -2538,7 +2538,7 @@ SELECT '' AS "64", d1 AS us_postgres FROM TIMESTAMP_TBL;
     | Mon Feb 10 17:32:01 1997
     | Mon Feb 10 17:32:01 1997
     | Mon Feb 10 17:32:01 1997
-    | Mon Feb 10 14:32:01 1997
+    | Mon Feb 10 17:32:01 1997
     | Tue Jun 10 18:32:01 1997
     | Mon Feb 10 17:32:01 1997
     | Tue Feb 11 17:32:01 1997
@@ -2621,7 +2621,7 @@ SELECT '' AS "64", d1 AS us_iso FROM TIMESTAMP_TBL;
     | 1997-02-10 17:32:01
     | 1997-02-10 17:32:01
     | 1997-02-10 17:32:01
-    | 1997-02-10 14:32:01
+    | 1997-02-10 17:32:01
     | 1997-06-10 18:32:01
     | 1997-02-10 17:32:01
     | 1997-02-11 17:32:01
@@ -2710,7 +2710,7 @@ SELECT '' AS "64", d1 AS us_sql FROM TIMESTAMP_TBL;
     | 02/10/1997 17:32:01
     | 02/10/1997 17:32:01
     | 02/10/1997 17:32:01
-    | 02/10/1997 14:32:01
+    | 02/10/1997 17:32:01
     | 06/10/1997 18:32:01
     | 02/10/1997 17:32:01
     | 02/11/1997 17:32:01
@@ -2806,7 +2806,7 @@ SELECT '' AS "65", d1 AS european_postgres FROM TIMESTAMP_TBL;
     | Mon 10 Feb 17:32:01 1997
     | Mon 10 Feb 17:32:01 1997
     | Mon 10 Feb 17:32:01 1997
-    | Mon 10 Feb 14:32:01 1997
+    | Mon 10 Feb 17:32:01 1997
     | Tue 10 Jun 18:32:01 1997
     | Mon 10 Feb 17:32:01 1997
     | Tue 11 Feb 17:32:01 1997
@@ -2896,7 +2896,7 @@ SELECT '' AS "65", d1 AS european_iso FROM TIMESTAMP_TBL;
     | 1997-02-10 17:32:01
     | 1997-02-10 17:32:01
     | 1997-02-10 17:32:01
-    | 1997-02-10 14:32:01
+    | 1997-02-10 17:32:01
     | 1997-06-10 18:32:01
     | 1997-02-10 17:32:01
     | 1997-02-11 17:32:01
@@ -2986,7 +2986,7 @@ SELECT '' AS "65", d1 AS european_sql FROM TIMESTAMP_TBL;
     | 10/02/1997 17:32:01
     | 10/02/1997 17:32:01
     | 10/02/1997 17:32:01
-    | 10/02/1997 14:32:01
+    | 10/02/1997 17:32:01
     | 10/06/1997 18:32:01
     | 10/02/1997 17:32:01
     | 11/02/1997 17:32:01
diff --git a/src/test/regress/expected/timestamp.out b/src/test/regress/expected/timestamp.out
index 5f3de34149a..c145279e73e 100644
--- a/src/test/regress/expected/timestamp.out
+++ b/src/test/regress/expected/timestamp.out
@@ -80,12 +80,12 @@ INSERT INTO TIMESTAMP_TBL VALUES ('1997-02-10 17:32:01 -08:00');
 INSERT INTO TIMESTAMP_TBL VALUES ('19970210 173201 -0800');
 INSERT INTO TIMESTAMP_TBL VALUES ('1997-06-10 17:32:01 -07:00');
 INSERT INTO TIMESTAMP_TBL VALUES ('2001-09-22T18:19:20');
--- POSIX format
+-- POSIX format (note that the timezone abbrev is just decoration here)
 INSERT INTO TIMESTAMP_TBL VALUES ('2000-03-15 08:14:01 GMT+8');
 INSERT INTO TIMESTAMP_TBL VALUES ('2000-03-15 13:14:02 GMT-1');
-INSERT INTO TIMESTAMP_TBL VALUES ('2000-03-15 12:14:03 GMT -2');
-INSERT INTO TIMESTAMP_TBL VALUES ('2000-03-15 03:14:04 EST+3');
-INSERT INTO TIMESTAMP_TBL VALUES ('2000-03-15 02:14:05 EST +2:00');
+INSERT INTO TIMESTAMP_TBL VALUES ('2000-03-15 12:14:03 GMT-2');
+INSERT INTO TIMESTAMP_TBL VALUES ('2000-03-15 03:14:04 PST+8');
+INSERT INTO TIMESTAMP_TBL VALUES ('2000-03-15 02:14:05 MST+7:00');
 -- Variations for acceptable input formats
 INSERT INTO TIMESTAMP_TBL VALUES ('Feb 10 17:32:01 1997 -0800');
 INSERT INTO TIMESTAMP_TBL VALUES ('Feb 10 17:32:01 1997');
@@ -101,9 +101,9 @@ INSERT INTO TIMESTAMP_TBL VALUES ('97/02/10 17:32:01 UTC');
 reset datestyle;
 INSERT INTO TIMESTAMP_TBL VALUES ('1997.041 17:32:01 UTC');
 INSERT INTO TIMESTAMP_TBL VALUES ('19970210 173201 America/New_York');
--- this fails
+-- this fails (even though TZ is a no-op, we still look it up)
 INSERT INTO TIMESTAMP_TBL VALUES ('19970710 173201 America/Does_not_exist');
-ERROR:  time zone "America/Does_not_exist" not recognized
+ERROR:  time zone "america/does_not_exist" not recognized
 -- Check date conversion and date arithmetic
 INSERT INTO TIMESTAMP_TBL VALUES ('1997-06-10 18:32:01 PDT');
 INSERT INTO TIMESTAMP_TBL VALUES ('Feb 10 17:32:01 1997');
@@ -179,7 +179,7 @@ SELECT '' AS "64", d1 FROM TIMESTAMP_TBL;
     | Mon Feb 10 17:32:01 1997
     | Mon Feb 10 17:32:01 1997
     | Mon Feb 10 17:32:01 1997
-    | Mon Feb 10 14:32:01 1997
+    | Mon Feb 10 17:32:01 1997
     | Tue Jun 10 18:32:01 1997
     | Mon Feb 10 17:32:01 1997
     | Tue Feb 11 17:32:01 1997
@@ -248,7 +248,7 @@ SELECT '' AS "48", d1 FROM TIMESTAMP_TBL
     | Mon Feb 10 17:32:01 1997
     | Mon Feb 10 17:32:01 1997
     | Mon Feb 10 17:32:01 1997
-    | Mon Feb 10 14:32:01 1997
+    | Mon Feb 10 17:32:01 1997
     | Tue Jun 10 18:32:01 1997
     | Mon Feb 10 17:32:01 1997
     | Tue Feb 11 17:32:01 1997
@@ -333,7 +333,7 @@ SELECT '' AS "63", d1 FROM TIMESTAMP_TBL
     | Mon Feb 10 17:32:01 1997
     | Mon Feb 10 17:32:01 1997
     | Mon Feb 10 17:32:01 1997
-    | Mon Feb 10 14:32:01 1997
+    | Mon Feb 10 17:32:01 1997
     | Tue Jun 10 18:32:01 1997
     | Mon Feb 10 17:32:01 1997
     | Tue Feb 11 17:32:01 1997
@@ -424,7 +424,7 @@ SELECT '' AS "49", d1 FROM TIMESTAMP_TBL
     | Mon Feb 10 17:32:01 1997
     | Mon Feb 10 17:32:01 1997
     | Mon Feb 10 17:32:01 1997
-    | Mon Feb 10 14:32:01 1997
+    | Mon Feb 10 17:32:01 1997
     | Tue Jun 10 18:32:01 1997
     | Mon Feb 10 17:32:01 1997
     | Tue Feb 11 17:32:01 1997
@@ -480,7 +480,7 @@ SELECT '' AS "54", d1 - timestamp without time zone '1997-01-02' AS diff
     | @ 39 days 17 hours 32 mins 1 sec
     | @ 39 days 17 hours 32 mins 1 sec
     | @ 39 days 17 hours 32 mins 1 sec
-    | @ 39 days 14 hours 32 mins 1 sec
+    | @ 39 days 17 hours 32 mins 1 sec
     | @ 159 days 18 hours 32 mins 1 sec
     | @ 39 days 17 hours 32 mins 1 sec
     | @ 40 days 17 hours 32 mins 1 sec
@@ -550,7 +550,7 @@ SELECT '' AS "54", d1 - timestamp without time zone '1997-01-02' AS diff
     | @ 39 days 17 hours 32 mins 1 sec
     | @ 39 days 17 hours 32 mins 1 sec
     | @ 39 days 17 hours 32 mins 1 sec
-    | @ 39 days 14 hours 32 mins 1 sec
+    | @ 39 days 17 hours 32 mins 1 sec
     | @ 159 days 18 hours 32 mins 1 sec
     | @ 39 days 17 hours 32 mins 1 sec
     | @ 40 days 17 hours 32 mins 1 sec
@@ -614,7 +614,7 @@ SELECT '' AS "54", d1 as "timestamp",
     | Mon Feb 10 17:32:01 1997    | 1997 |     2 |  10 |   17 |     32 |      1
     | Mon Feb 10 17:32:01 1997    | 1997 |     2 |  10 |   17 |     32 |      1
     | Mon Feb 10 17:32:01 1997    | 1997 |     2 |  10 |   17 |     32 |      1
-    | Mon Feb 10 14:32:01 1997    | 1997 |     2 |  10 |   14 |     32 |      1
+    | Mon Feb 10 17:32:01 1997    | 1997 |     2 |  10 |   17 |     32 |      1
     | Tue Jun 10 18:32:01 1997    | 1997 |     6 |  10 |   18 |     32 |      1
     | Mon Feb 10 17:32:01 1997    | 1997 |     2 |  10 |   17 |     32 |      1
     | Tue Feb 11 17:32:01 1997    | 1997 |     2 |  11 |   17 |     32 |      1
@@ -677,7 +677,7 @@ SELECT '' AS "54", d1 as "timestamp",
     | Mon Feb 10 17:32:01 1997    |       1 |  1000 |  1000000
     | Mon Feb 10 17:32:01 1997    |       1 |  1000 |  1000000
     | Mon Feb 10 17:32:01 1997    |       1 |  1000 |  1000000
-    | Mon Feb 10 14:32:01 1997    |       1 |  1000 |  1000000
+    | Mon Feb 10 17:32:01 1997    |       1 |  1000 |  1000000
     | Tue Jun 10 18:32:01 1997    |       2 |  1000 |  1000000
     | Mon Feb 10 17:32:01 1997    |       1 |  1000 |  1000000
     | Tue Feb 11 17:32:01 1997    |       1 |  1000 |  1000000
@@ -1025,7 +1025,7 @@ SELECT '' AS to_char_5, to_char(d1, 'HH HH12 HH24 MI SS SSSS')
            | 05 05 17 32 01 63121
            | 05 05 17 32 01 63121
            | 05 05 17 32 01 63121
-           | 02 02 14 32 01 52321
+           | 05 05 17 32 01 63121
            | 06 06 18 32 01 66721
            | 05 05 17 32 01 63121
            | 05 05 17 32 01 63121
@@ -1096,7 +1096,7 @@ SELECT '' AS to_char_6, to_char(d1, E'"HH:MI:SS is" HH:MI:SS "\\"text between qu
            | HH:MI:SS is 05:32:01 "text between quote marks"
            | HH:MI:SS is 05:32:01 "text between quote marks"
            | HH:MI:SS is 05:32:01 "text between quote marks"
-           | HH:MI:SS is 02:32:01 "text between quote marks"
+           | HH:MI:SS is 05:32:01 "text between quote marks"
            | HH:MI:SS is 06:32:01 "text between quote marks"
            | HH:MI:SS is 05:32:01 "text between quote marks"
            | HH:MI:SS is 05:32:01 "text between quote marks"
@@ -1167,7 +1167,7 @@ SELECT '' AS to_char_7, to_char(d1, 'HH24--text--MI--text--SS')
            | 17--text--32--text--01
            | 17--text--32--text--01
            | 17--text--32--text--01
-           | 14--text--32--text--01
+           | 17--text--32--text--01
            | 18--text--32--text--01
            | 17--text--32--text--01
            | 17--text--32--text--01
@@ -1310,7 +1310,7 @@ SELECT '' AS to_char_9, to_char(d1, 'YYYY A.D. YYYY a.d. YYYY bc HH:MI:SS P.M. H
            | 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
            | 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
            | 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
-           | 1997 A.D. 1997 a.d. 1997 ad 02:32:01 P.M. 02:32:01 p.m. 02:32:01 pm
+           | 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
            | 1997 A.D. 1997 a.d. 1997 ad 06:32:01 P.M. 06:32:01 p.m. 06:32:01 pm
            | 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
            | 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
diff --git a/src/test/regress/expected/timestamptz.out b/src/test/regress/expected/timestamptz.out
index 4448ef6e20a..4a17503324d 100644
--- a/src/test/regress/expected/timestamptz.out
+++ b/src/test/regress/expected/timestamptz.out
@@ -75,12 +75,12 @@ INSERT INTO TIMESTAMPTZ_TBL VALUES ('1997-02-10 17:32:01 -08:00');
 INSERT INTO TIMESTAMPTZ_TBL VALUES ('19970210 173201 -0800');
 INSERT INTO TIMESTAMPTZ_TBL VALUES ('1997-06-10 17:32:01 -07:00');
 INSERT INTO TIMESTAMPTZ_TBL VALUES ('2001-09-22T18:19:20');
--- POSIX format
+-- POSIX format (note that the timezone abbrev is just decoration here)
 INSERT INTO TIMESTAMPTZ_TBL VALUES ('2000-03-15 08:14:01 GMT+8');
 INSERT INTO TIMESTAMPTZ_TBL VALUES ('2000-03-15 13:14:02 GMT-1');
-INSERT INTO TIMESTAMPTZ_TBL VALUES ('2000-03-15 12:14:03 GMT -2');
-INSERT INTO TIMESTAMPTZ_TBL VALUES ('2000-03-15 03:14:04 EST+3');
-INSERT INTO TIMESTAMPTZ_TBL VALUES ('2000-03-15 02:14:05 EST +2:00');
+INSERT INTO TIMESTAMPTZ_TBL VALUES ('2000-03-15 12:14:03 GMT-2');
+INSERT INTO TIMESTAMPTZ_TBL VALUES ('2000-03-15 03:14:04 PST+8');
+INSERT INTO TIMESTAMPTZ_TBL VALUES ('2000-03-15 02:14:05 MST+7:00');
 -- Variations for acceptable input formats
 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 10 17:32:01 1997 -0800');
 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 10 17:32:01 1997');
@@ -111,7 +111,7 @@ SELECT '19970710 173201' AT TIME ZONE 'America/New_York';
 (1 row)
 
 INSERT INTO TIMESTAMPTZ_TBL VALUES ('19970710 173201 America/Does_not_exist');
-ERROR:  time zone "America/Does_not_exist" not recognized
+ERROR:  time zone "america/does_not_exist" not recognized
 SELECT '19970710 173201' AT TIME ZONE 'America/Does_not_exist';
 ERROR:  time zone "America/Does_not_exist" not recognized
 -- Check date conversion and date arithmetic
diff --git a/src/test/regress/sql/timestamp.sql b/src/test/regress/sql/timestamp.sql
index 31235704a9c..34689564dae 100644
--- a/src/test/regress/sql/timestamp.sql
+++ b/src/test/regress/sql/timestamp.sql
@@ -62,12 +62,12 @@ INSERT INTO TIMESTAMP_TBL VALUES ('19970210 173201 -0800');
 INSERT INTO TIMESTAMP_TBL VALUES ('1997-06-10 17:32:01 -07:00');
 INSERT INTO TIMESTAMP_TBL VALUES ('2001-09-22T18:19:20');
 
--- POSIX format
+-- POSIX format (note that the timezone abbrev is just decoration here)
 INSERT INTO TIMESTAMP_TBL VALUES ('2000-03-15 08:14:01 GMT+8');
 INSERT INTO TIMESTAMP_TBL VALUES ('2000-03-15 13:14:02 GMT-1');
-INSERT INTO TIMESTAMP_TBL VALUES ('2000-03-15 12:14:03 GMT -2');
-INSERT INTO TIMESTAMP_TBL VALUES ('2000-03-15 03:14:04 EST+3');
-INSERT INTO TIMESTAMP_TBL VALUES ('2000-03-15 02:14:05 EST +2:00');
+INSERT INTO TIMESTAMP_TBL VALUES ('2000-03-15 12:14:03 GMT-2');
+INSERT INTO TIMESTAMP_TBL VALUES ('2000-03-15 03:14:04 PST+8');
+INSERT INTO TIMESTAMP_TBL VALUES ('2000-03-15 02:14:05 MST+7:00');
 
 -- Variations for acceptable input formats
 INSERT INTO TIMESTAMP_TBL VALUES ('Feb 10 17:32:01 1997 -0800');
@@ -83,12 +83,10 @@ INSERT INTO TIMESTAMP_TBL VALUES ('97FEB10 5:32:01PM UTC');
 INSERT INTO TIMESTAMP_TBL VALUES ('97/02/10 17:32:01 UTC');
 reset datestyle;
 INSERT INTO TIMESTAMP_TBL VALUES ('1997.041 17:32:01 UTC');
-
 INSERT INTO TIMESTAMP_TBL VALUES ('19970210 173201 America/New_York');
--- this fails
+-- this fails (even though TZ is a no-op, we still look it up)
 INSERT INTO TIMESTAMP_TBL VALUES ('19970710 173201 America/Does_not_exist');
 
-
 -- Check date conversion and date arithmetic
 INSERT INTO TIMESTAMP_TBL VALUES ('1997-06-10 18:32:01 PDT');
 
diff --git a/src/test/regress/sql/timestamptz.sql b/src/test/regress/sql/timestamptz.sql
index 65af1b04173..fc597a6b2cf 100644
--- a/src/test/regress/sql/timestamptz.sql
+++ b/src/test/regress/sql/timestamptz.sql
@@ -56,12 +56,12 @@ INSERT INTO TIMESTAMPTZ_TBL VALUES ('19970210 173201 -0800');
 INSERT INTO TIMESTAMPTZ_TBL VALUES ('1997-06-10 17:32:01 -07:00');
 INSERT INTO TIMESTAMPTZ_TBL VALUES ('2001-09-22T18:19:20');
 
--- POSIX format
+-- POSIX format (note that the timezone abbrev is just decoration here)
 INSERT INTO TIMESTAMPTZ_TBL VALUES ('2000-03-15 08:14:01 GMT+8');
 INSERT INTO TIMESTAMPTZ_TBL VALUES ('2000-03-15 13:14:02 GMT-1');
-INSERT INTO TIMESTAMPTZ_TBL VALUES ('2000-03-15 12:14:03 GMT -2');
-INSERT INTO TIMESTAMPTZ_TBL VALUES ('2000-03-15 03:14:04 EST+3');
-INSERT INTO TIMESTAMPTZ_TBL VALUES ('2000-03-15 02:14:05 EST +2:00');
+INSERT INTO TIMESTAMPTZ_TBL VALUES ('2000-03-15 12:14:03 GMT-2');
+INSERT INTO TIMESTAMPTZ_TBL VALUES ('2000-03-15 03:14:04 PST+8');
+INSERT INTO TIMESTAMPTZ_TBL VALUES ('2000-03-15 02:14:05 MST+7:00');
 
 -- Variations for acceptable input formats
 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 10 17:32:01 1997 -0800');