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.

5 files changed, 44 insertions, 46 deletions

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