Re: Support TZ format code in to_timestamp()

Aleksander Alekseev <aleksander@timescale.com> writes:
> I reviewed the patch and tested it on MacOS and generally concur with
> stated above. The only nitpick I have is the apparent lack of negative
> tests for to_timestamp(), e.g. when the string doesn't match the
> specified format.

That's an excellent suggestion indeed, because when I tried

SELECT to_timestamp('2011-12-18 11:38 JUNK', 'YYYY-MM-DD HH12:MI TZ');  -- error

I got

ERROR:  invalid value "JU" for "TZ"
DETAIL:  Value must be an integer.

which seems pretty off-point.  In the attached I made it give an
error message about a bad zone abbreviation if the input starts
with a letter, but perhaps the dividing line between "probably
meant as a zone name" and "probably meant as numeric" should be
drawn differently?

Anyway, v2-0001 below is the previous patch rebased up to current
(only line numbers change), and then v2-0002 responds to your
and Daniel's review comments.

            regards, tom lane

From 8361441cda3343e0f9286bb3b51ccd3041b46807 Mon Sep 17 00:00:00 2001
From: Tom Lane <tgl@sss.pgh.pa.us>
Date: Tue, 23 Jan 2024 16:58:19 -0500
Subject: [PATCH v2 1/2] Support timezone abbreviations in to_timestamp().

This patch allows the TZ format code to be used in to_timestamp().
It will accept the same possibilities that to_char() can produce
with the TZ format code, namely a zone abbreviation or a numeric
zone offset in the form [+-]HH or [+-]HH:MM.

While at it we may as well implement the OF format code too,
since it corresponds exactly to the numeric zone offset case
and indeed can share code.

A conceivable extension to this would be to accept timezone names
not just abbreviations.  However, to_char() never outputs those,
and there'd be a pretty serious garbage-input hazard because
pg_tzset() will accept just about anything as a POSIX zone name.

A useful side effect is that jsonpath's datetime() method will
now accept the common-in-JSON format "yyyy-mm-ddThh:mm:ssZ",
correctly interpreting the "Z" as signifying UTC time.  We can
reduce the number of format patterns that executeDateTimeMethod
has to try, too.
---
 doc/src/sgml/func.sgml                       |  10 +-
 src/backend/utils/adt/datetime.c             |  76 ++++++++++
 src/backend/utils/adt/formatting.c           | 151 ++++++++++++-------
 src/backend/utils/adt/jsonpath_exec.c        |  18 +--
 src/include/utils/datetime.h                 |   3 +
 src/test/regress/expected/horology.out       |  70 ++++++++-
 src/test/regress/expected/jsonb_jsonpath.out |  12 ++
 src/test/regress/sql/horology.sql            |  18 ++-
 src/test/regress/sql/jsonb_jsonpath.sql      |   2 +
 9 files changed, 287 insertions(+), 73 deletions(-)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 210c7c0b02..ad965432f3 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -8131,13 +8131,11 @@ SELECT regexp_match('abc01234xyz', '(?:(.*?)(\d+)(.*)){1,1}');
        </row>
        <row>
         <entry><literal>TZ</literal></entry>
-        <entry>upper case time-zone abbreviation
-         (only supported in <function>to_char</function>)</entry>
+        <entry>upper case time-zone abbreviation</entry>
        </row>
        <row>
         <entry><literal>tz</literal></entry>
-        <entry>lower case time-zone abbreviation
-         (only supported in <function>to_char</function>)</entry>
+        <entry>lower case time-zone abbreviation</entry>
        </row>
        <row>
        <entry><literal>TZH</literal></entry>
@@ -8149,8 +8147,8 @@ SELECT regexp_match('abc01234xyz', '(?:(.*?)(\d+)(.*)){1,1}');
        </row>
        <row>
         <entry><literal>OF</literal></entry>
-        <entry>time-zone offset from UTC
-         (only supported in <function>to_char</function>)</entry>
+        <entry>time-zone offset from UTC (<replaceable>HH</replaceable>
+         or <replaceable>HH</replaceable><literal>:</literal><replaceable>MM</replaceable>)</entry>
        </row>
       </tbody>
      </tgroup>
diff --git a/src/backend/utils/adt/datetime.c b/src/backend/utils/adt/datetime.c
index 17b0248bf7..cccabb0c2a 100644
--- a/src/backend/utils/adt/datetime.c
+++ b/src/backend/utils/adt/datetime.c
@@ -3246,6 +3246,82 @@ DecodeTimezoneNameToTz(const char *tzname)
     return result;
 }

+/* DecodeTimezoneAbbrevPrefix()
+ * Interpret prefix of string as a timezone abbreviation, if possible.
+ *
+ * This has roughly the same functionality as DecodeTimezoneAbbrev(),
+ * but the API is adapted to the needs of formatting.c.  Notably,
+ * we will match the longest possible prefix of the given string
+ * rather than insisting on a complete match, and downcasing is applied
+ * here rather than in the caller.
+ *
+ * Returns the length of the timezone abbreviation, or -1 if not recognized.
+ * On success, sets *offset to the GMT offset for the abbreviation if it
+ * is a fixed-offset abbreviation, or sets *tz to the pg_tz struct for
+ * a dynamic abbreviation.
+ */
+int
+DecodeTimezoneAbbrevPrefix(const char *str, int *offset, pg_tz **tz)
+{
+    char        lowtoken[TOKMAXLEN + 1];
+    int            len;
+
+    *offset = 0;                /* avoid uninitialized vars on failure */
+    *tz = NULL;
+
+    if (!zoneabbrevtbl)
+        return -1;                /* no abbrevs known, so fail immediately */
+
+    /* Downcase as much of the string as we could need */
+    for (len = 0; len < TOKMAXLEN; len++)
+    {
+        if (*str == '\0' || !isalpha((unsigned char) *str))
+            break;
+        lowtoken[len] = pg_tolower((unsigned char) *str++);
+    }
+    lowtoken[len] = '\0';
+
+    /*
+     * We could avoid doing repeated binary searches if we cared to duplicate
+     * datebsearch here, but it's not clear that such an optimization would be
+     * worth the trouble.  In common cases there's probably not anything after
+     * the zone abbrev anyway.  So just search with successively truncated
+     * strings.
+     */
+    while (len > 0)
+    {
+        const datetkn *tp = datebsearch(lowtoken, zoneabbrevtbl->abbrevs,
+                                        zoneabbrevtbl->numabbrevs);
+
+        if (tp != NULL)
+        {
+            if (tp->type == DYNTZ)
+            {
+                DateTimeErrorExtra extra;
+                pg_tz       *tzp = FetchDynamicTimeZone(zoneabbrevtbl, tp,
+                                                       &extra);
+
+                if (tzp != NULL)
+                {
+                    /* Caller must resolve the abbrev's current meaning */
+                    *tz = tzp;
+                    return len;
+                }
+            }
+            else
+            {
+                /* Fixed-offset zone abbrev, so it's easy */
+                *offset = tp->value;
+                return len;
+            }
+        }
+        lowtoken[--len] = '\0';
+    }
+
+    /* Did not find a match */
+    return -1;
+}
+

 /* ClearPgItmIn
  *
diff --git a/src/backend/utils/adt/formatting.c b/src/backend/utils/adt/formatting.c
index 83e1f1265c..8859d174c2 100644
--- a/src/backend/utils/adt/formatting.c
+++ b/src/backend/utils/adt/formatting.c
@@ -418,14 +418,24 @@ typedef struct
                 us,
                 yysz,            /* is it YY or YYYY ? */
                 clock,            /* 12 or 24 hour clock? */
-                tzsign,            /* +1, -1 or 0 if timezone info is absent */
+                tzsign,            /* +1, -1, or 0 if no TZH/TZM fields */
                 tzh,
                 tzm,
                 ff;                /* fractional precision */
+    bool        has_tz;            /* was there a TZ field? */
+    int            gmtoffset;        /* GMT offset of fixed-offset zone abbrev */
+    pg_tz       *tzp;            /* pg_tz for dynamic abbrev */
+    char       *abbrev;            /* dynamic abbrev */
 } TmFromChar;

 #define ZERO_tmfc(_X) memset(_X, 0, sizeof(TmFromChar))

+struct fmt_tz                    /* do_to_timestamp's timezone info output */
+{
+    bool        has_tz;            /* was there any TZ/TZH/TZM field? */
+    int            gmtoffset;        /* GMT offset in seconds */
+};
+
 /* ----------
  * Debug
  * ----------
@@ -1058,8 +1068,8 @@ static bool from_char_seq_search(int *dest, const char **src,
                                  char **localized_array, Oid collid,
                                  FormatNode *node, Node *escontext);
 static bool do_to_timestamp(text *date_txt, text *fmt, Oid collid, bool std,
-                            struct pg_tm *tm, fsec_t *fsec, int *fprec,
-                            uint32 *flags, Node *escontext);
+                            struct pg_tm *tm, fsec_t *fsec, struct fmt_tz *tz,
+                            int *fprec, uint32 *flags, Node *escontext);
 static char *fill_str(char *str, int c, int max);
 static FormatNode *NUM_cache(int len, NUMDesc *Num, text *pars_str, bool *shouldFree);
 static char *int_to_roman(int number);
@@ -3467,11 +3477,49 @@ DCH_from_char(FormatNode *node, const char *in, TmFromChar *out,
                 break;
             case DCH_tz:
             case DCH_TZ:
+                {
+                    int            tzlen;
+
+                    tzlen = DecodeTimezoneAbbrevPrefix(s,
+                                                       &out->gmtoffset,
+                                                       &out->tzp);
+                    if (tzlen > 0)
+                    {
+                        out->has_tz = true;
+                        /* we only need the zone abbrev for DYNTZ case */
+                        if (out->tzp)
+                            out->abbrev = pnstrdup(s, tzlen);
+                        out->tzsign = 0;    /* drop any earlier TZH/TZM info */
+                        s += tzlen;
+                        break;
+                    }
+                }
+                /* it doesn't match any abbrev, so parse it like OF */
+                /* FALL THRU */
             case DCH_OF:
-                ereturn(escontext,,
-                        (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
-                         errmsg("formatting field \"%s\" is only supported in to_char",
-                                n->key->name)));
+                /* OF is equivalent to TZH or TZH:TZM */
+                /* see TZH comments below */
+                if (*s == '+' || *s == '-' || *s == ' ')
+                {
+                    out->tzsign = *s == '-' ? -1 : +1;
+                    s++;
+                }
+                else
+                {
+                    if (extra_skip > 0 && *(s - 1) == '-')
+                        out->tzsign = -1;
+                    else
+                        out->tzsign = +1;
+                }
+                if (from_char_parse_int_len(&out->tzh, &s, 2, n, escontext) < 0)
+                    return;
+                if (*s == ':')
+                {
+                    s++;
+                    if (from_char_parse_int_len(&out->tzm, &s, 2, n,
+                                                escontext) < 0)
+                        return;
+                }
                 break;
             case DCH_TZH:

@@ -4167,22 +4215,16 @@ to_timestamp(PG_FUNCTION_ARGS)
     Timestamp    result;
     int            tz;
     struct pg_tm tm;
+    struct fmt_tz ftz;
     fsec_t        fsec;
     int            fprec;

     do_to_timestamp(date_txt, fmt, collid, false,
-                    &tm, &fsec, &fprec, NULL, NULL);
+                    &tm, &fsec, &ftz, &fprec, NULL, NULL);

     /* Use the specified time zone, if any. */
-    if (tm.tm_zone)
-    {
-        DateTimeErrorExtra extra;
-        int            dterr = DecodeTimezone(tm.tm_zone, &tz);
-
-        if (dterr)
-            DateTimeParseError(dterr, &extra, text_to_cstring(date_txt),
-                               "timestamptz", NULL);
-    }
+    if (ftz.has_tz)
+        tz = ftz.gmtoffset;
     else
         tz = DetermineTimeZoneOffset(&tm, session_timezone);

@@ -4211,10 +4253,11 @@ to_date(PG_FUNCTION_ARGS)
     Oid            collid = PG_GET_COLLATION();
     DateADT        result;
     struct pg_tm tm;
+    struct fmt_tz ftz;
     fsec_t        fsec;

     do_to_timestamp(date_txt, fmt, collid, false,
-                    &tm, &fsec, NULL, NULL, NULL);
+                    &tm, &fsec, &ftz, NULL, NULL, NULL);

     /* Prevent overflow in Julian-day routines */
     if (!IS_VALID_JULIAN(tm.tm_year, tm.tm_mon, tm.tm_mday))
@@ -4256,12 +4299,13 @@ parse_datetime(text *date_txt, text *fmt, Oid collid, bool strict,
                Node *escontext)
 {
     struct pg_tm tm;
+    struct fmt_tz ftz;
     fsec_t        fsec;
     int            fprec;
     uint32        flags;

     if (!do_to_timestamp(date_txt, fmt, collid, strict,
-                         &tm, &fsec, &fprec, &flags, escontext))
+                         &tm, &fsec, &ftz, &fprec, &flags, escontext))
         return (Datum) 0;

     *typmod = fprec ? fprec : -1;    /* fractional part precision */
@@ -4274,18 +4318,9 @@ parse_datetime(text *date_txt, text *fmt, Oid collid, bool strict,
             {
                 TimestampTz result;

-                if (tm.tm_zone)
+                if (ftz.has_tz)
                 {
-                    DateTimeErrorExtra extra;
-                    int            dterr = DecodeTimezone(tm.tm_zone, tz);
-
-                    if (dterr)
-                    {
-                        DateTimeParseError(dterr, &extra,
-                                           text_to_cstring(date_txt),
-                                           "timestamptz", escontext);
-                        return (Datum) 0;
-                    }
+                    *tz = ftz.gmtoffset;
                 }
                 else
                 {
@@ -4366,18 +4401,9 @@ parse_datetime(text *date_txt, text *fmt, Oid collid, bool strict,
         {
             TimeTzADT  *result = palloc(sizeof(TimeTzADT));

-            if (tm.tm_zone)
+            if (ftz.has_tz)
             {
-                DateTimeErrorExtra extra;
-                int            dterr = DecodeTimezone(tm.tm_zone, tz);
-
-                if (dterr)
-                {
-                    DateTimeParseError(dterr, &extra,
-                                       text_to_cstring(date_txt),
-                                       "timetz", escontext);
-                    return (Datum) 0;
-                }
+                *tz = ftz.gmtoffset;
             }
             else
             {
@@ -4430,7 +4456,7 @@ parse_datetime(text *date_txt, text *fmt, Oid collid, bool strict,
  * do_to_timestamp: shared code for to_timestamp and to_date
  *
  * Parse the 'date_txt' according to 'fmt', return results as a struct pg_tm,
- * fractional seconds, and fractional precision.
+ * fractional seconds, struct fmt_tz, and fractional precision.
  *
  * 'collid' identifies the collation to use, if needed.
  * 'std' specifies standard parsing mode.
@@ -4447,12 +4473,12 @@ parse_datetime(text *date_txt, text *fmt, Oid collid, bool strict,
  * 'date_txt'.
  *
  * The TmFromChar is then analysed and converted into the final results in
- * struct 'tm', 'fsec', and 'fprec'.
+ * struct 'tm', 'fsec', struct 'tz', and 'fprec'.
  */
 static bool
 do_to_timestamp(text *date_txt, text *fmt, Oid collid, bool std,
-                struct pg_tm *tm, fsec_t *fsec, int *fprec,
-                uint32 *flags, Node *escontext)
+                struct pg_tm *tm, fsec_t *fsec, struct fmt_tz *tz,
+                int *fprec, uint32 *flags, Node *escontext)
 {
     FormatNode *format = NULL;
     TmFromChar    tmfc;
@@ -4469,6 +4495,7 @@ do_to_timestamp(text *date_txt, text *fmt, Oid collid, bool std,
     ZERO_tmfc(&tmfc);
     ZERO_tm(tm);
     *fsec = 0;
+    tz->has_tz = false;
     if (fprec)
         *fprec = 0;
     if (flags)
@@ -4744,11 +4771,14 @@ do_to_timestamp(text *date_txt, text *fmt, Oid collid, bool std,
         goto fail;
     }

-    /* Save parsed time-zone into tm->tm_zone if it was specified */
+    /*
+     * If timezone info was present, reduce it to a GMT offset.  (We cannot do
+     * this until we've filled all of the tm struct, since the zone's offset
+     * might be time-varying.)
+     */
     if (tmfc.tzsign)
     {
-        char       *tz;
-
+        /* TZH and/or TZM fields */
         if (tmfc.tzh < 0 || tmfc.tzh > MAX_TZDISP_HOUR ||
             tmfc.tzm < 0 || tmfc.tzm >= MINS_PER_HOUR)
         {
@@ -4757,10 +4787,27 @@ do_to_timestamp(text *date_txt, text *fmt, Oid collid, bool std,
             goto fail;
         }

-        tz = psprintf("%c%02d:%02d",
-                      tmfc.tzsign > 0 ? '+' : '-', tmfc.tzh, tmfc.tzm);
-
-        tm->tm_zone = tz;
+        tz->has_tz = true;
+        tz->gmtoffset = (tmfc.tzh * MINS_PER_HOUR + tmfc.tzm) * SECS_PER_MINUTE;
+        /* note we are flipping the sign convention here */
+        if (tmfc.tzsign > 0)
+            tz->gmtoffset = -tz->gmtoffset;
+    }
+    else if (tmfc.has_tz)
+    {
+        /* TZ field */
+        tz->has_tz = true;
+        if (tmfc.tzp == NULL)
+        {
+            /* fixed-offset abbreviation; flip the sign convention */
+            tz->gmtoffset = -tmfc.gmtoffset;
+        }
+        else
+        {
+            /* dynamic-offset abbreviation, resolve using specified time */
+            tz->gmtoffset = DetermineTimeZoneAbbrevOffset(tm, tmfc.abbrev,
+                                                          tmfc.tzp);
+        }
     }

     DEBUG_TM(tm);
diff --git a/src/backend/utils/adt/jsonpath_exec.c b/src/backend/utils/adt/jsonpath_exec.c
index ac16f5c85d..1db1ffe3c8 100644
--- a/src/backend/utils/adt/jsonpath_exec.c
+++ b/src/backend/utils/adt/jsonpath_exec.c
@@ -1846,20 +1846,14 @@ executeDateTimeMethod(JsonPathExecContext *cxt, JsonPathItem *jsp,
         static const char *fmt_str[] =
         {
             "yyyy-mm-dd",        /* date */
-            "HH24:MI:SS.USTZH:TZM", /* timetz */
-            "HH24:MI:SS.USTZH",
-            "HH24:MI:SSTZH:TZM",
-            "HH24:MI:SSTZH",
+            "HH24:MI:SS.USTZ",    /* timetz */
+            "HH24:MI:SSTZ",
             "HH24:MI:SS.US",    /* time without tz */
             "HH24:MI:SS",
-            "yyyy-mm-dd HH24:MI:SS.USTZH:TZM",    /* timestamptz */
-            "yyyy-mm-dd HH24:MI:SS.USTZH",
-            "yyyy-mm-dd HH24:MI:SSTZH:TZM",
-            "yyyy-mm-dd HH24:MI:SSTZH",
-            "yyyy-mm-dd\"T\"HH24:MI:SS.USTZH:TZM",
-            "yyyy-mm-dd\"T\"HH24:MI:SS.USTZH",
-            "yyyy-mm-dd\"T\"HH24:MI:SSTZH:TZM",
-            "yyyy-mm-dd\"T\"HH24:MI:SSTZH",
+            "yyyy-mm-dd HH24:MI:SS.USTZ",    /* timestamptz */
+            "yyyy-mm-dd HH24:MI:SSTZ",
+            "yyyy-mm-dd\"T\"HH24:MI:SS.USTZ",
+            "yyyy-mm-dd\"T\"HH24:MI:SSTZ",
             "yyyy-mm-dd HH24:MI:SS.US", /* timestamp without tz */
             "yyyy-mm-dd HH24:MI:SS",
             "yyyy-mm-dd\"T\"HH24:MI:SS.US",
diff --git a/src/include/utils/datetime.h b/src/include/utils/datetime.h
index 460c75cfdd..e4ac2b8e7f 100644
--- a/src/include/utils/datetime.h
+++ b/src/include/utils/datetime.h
@@ -348,6 +348,9 @@ extern int    DecodeUnits(int field, const char *lowtoken, int *val);
 extern int    DecodeTimezoneName(const char *tzname, int *offset, pg_tz **tz);
 extern pg_tz *DecodeTimezoneNameToTz(const char *tzname);

+extern int    DecodeTimezoneAbbrevPrefix(const char *str,
+                                       int *offset, pg_tz **tz);
+
 extern int    j2day(int date);

 extern struct Node *TemporalSimplify(int32 max_precis, struct Node *node);
diff --git a/src/test/regress/expected/horology.out b/src/test/regress/expected/horology.out
index cfb4b205e4..142a2109ba 100644
--- a/src/test/regress/expected/horology.out
+++ b/src/test/regress/expected/horology.out
@@ -3140,8 +3140,54 @@ SELECT to_timestamp('2011-12-18 11:38 20',     'YYYY-MM-DD HH12:MI TZM');
  Sun Dec 18 03:18:00 2011 PST
 (1 row)

-SELECT to_timestamp('2011-12-18 11:38 PST', 'YYYY-MM-DD HH12:MI TZ');  -- NYI
-ERROR:  formatting field "TZ" is only supported in to_char
+SELECT to_timestamp('2011-12-18 11:38 EST', 'YYYY-MM-DD HH12:MI TZ');
+         to_timestamp
+------------------------------
+ Sun Dec 18 08:38:00 2011 PST
+(1 row)
+
+SELECT to_timestamp('2011-12-18 11:38 -05', 'YYYY-MM-DD HH12:MI TZ');
+         to_timestamp
+------------------------------
+ Sun Dec 18 08:38:00 2011 PST
+(1 row)
+
+SELECT to_timestamp('2011-12-18 11:38 +01:30', 'YYYY-MM-DD HH12:MI TZ');
+         to_timestamp
+------------------------------
+ Sun Dec 18 02:08:00 2011 PST
+(1 row)
+
+SELECT to_timestamp('2011-12-18 11:38 MSK', 'YYYY-MM-DD HH12:MI TZ');  -- dyntz
+         to_timestamp
+------------------------------
+ Sat Dec 17 23:38:00 2011 PST
+(1 row)
+
+SELECT to_timestamp('2011-12-18 11:38ESTFOO24', 'YYYY-MM-DD HH12:MITZFOOSS');
+         to_timestamp
+------------------------------
+ Sun Dec 18 08:38:24 2011 PST
+(1 row)
+
+SELECT to_timestamp('2011-12-18 11:38-05FOO24', 'YYYY-MM-DD HH12:MITZFOOSS');
+         to_timestamp
+------------------------------
+ Sun Dec 18 08:38:24 2011 PST
+(1 row)
+
+SELECT to_timestamp('2011-12-18 11:38 -05', 'YYYY-MM-DD HH12:MI OF');
+         to_timestamp
+------------------------------
+ Sun Dec 18 08:38:00 2011 PST
+(1 row)
+
+SELECT to_timestamp('2011-12-18 11:38 +01:30', 'YYYY-MM-DD HH12:MI OF');
+         to_timestamp
+------------------------------
+ Sun Dec 18 02:08:00 2011 PST
+(1 row)
+
 SELECT to_timestamp('2018-11-02 12:34:56.025', 'YYYY-MM-DD HH24:MI:SS.MS');
            to_timestamp
 ----------------------------------
@@ -3557,6 +3603,19 @@ SELECT to_date('0000-02-01','YYYY-MM-DD');  -- allowed, though it shouldn't be
  02-01-0001 BC
 (1 row)

+-- to_char's TZ format code produces zone abbrev if known
+SELECT to_char('2012-12-12 12:00'::timestamptz, 'YYYY-MM-DD HH:MI:SS TZ');
+         to_char
+-------------------------
+ 2012-12-12 12:00:00 PST
+(1 row)
+
+SELECT to_char('2012-12-12 12:00'::timestamptz, 'YYYY-MM-DD HH:MI:SS tz');
+         to_char
+-------------------------
+ 2012-12-12 12:00:00 pst
+(1 row)
+
 --
 -- Check behavior with SQL-style fixed-GMT-offset time zone (cf bug #8572)
 --
@@ -3598,4 +3657,11 @@ SELECT to_char('2012-12-12 12:00'::timestamptz, 'YYYY-MM-DD SSSSS');
  2012-12-12 43200
 (1 row)

+SET TIME ZONE '+2';
+SELECT to_char('2012-12-12 12:00'::timestamptz, 'YYYY-MM-DD HH:MI:SS TZ');
+         to_char
+-------------------------
+ 2012-12-12 12:00:00 +02
+(1 row)
+
 RESET TIME ZONE;
diff --git a/src/test/regress/expected/jsonb_jsonpath.out b/src/test/regress/expected/jsonb_jsonpath.out
index 6659bc9091..783a6f51bf 100644
--- a/src/test/regress/expected/jsonb_jsonpath.out
+++ b/src/test/regress/expected/jsonb_jsonpath.out
@@ -1935,6 +1935,18 @@ select jsonb_path_query('"2017-03-10T12:34:56.789+3:10"', '$.datetime()');
 select jsonb_path_query('"2017-03-10t12:34:56.789+3:10"', '$.datetime()');
 ERROR:  datetime format is not recognized: "2017-03-10t12:34:56.789+3:10"
 HINT:  Use a datetime template argument to specify the input data format.
+select jsonb_path_query('"2017-03-10T12:34:56.789EST"', '$.datetime()');
+        jsonb_path_query
+---------------------------------
+ "2017-03-10T12:34:56.789-05:00"
+(1 row)
+
+select jsonb_path_query('"2017-03-10T12:34:56.789Z"', '$.datetime()');
+        jsonb_path_query
+---------------------------------
+ "2017-03-10T12:34:56.789+00:00"
+(1 row)
+
 select jsonb_path_query('"12:34:56"', '$.datetime().type()');
      jsonb_path_query
 --------------------------
diff --git a/src/test/regress/sql/horology.sql b/src/test/regress/sql/horology.sql
index 252bce4b1c..fe647af976 100644
--- a/src/test/regress/sql/horology.sql
+++ b/src/test/regress/sql/horology.sql
@@ -501,7 +501,15 @@ SELECT to_timestamp('2011-12-18 11:38 +05:20', 'YYYY-MM-DD HH12:MI TZH:TZM');
 SELECT to_timestamp('2011-12-18 11:38 -05:20', 'YYYY-MM-DD HH12:MI TZH:TZM');
 SELECT to_timestamp('2011-12-18 11:38 20',     'YYYY-MM-DD HH12:MI TZM');

-SELECT to_timestamp('2011-12-18 11:38 PST', 'YYYY-MM-DD HH12:MI TZ');  -- NYI
+SELECT to_timestamp('2011-12-18 11:38 EST', 'YYYY-MM-DD HH12:MI TZ');
+SELECT to_timestamp('2011-12-18 11:38 -05', 'YYYY-MM-DD HH12:MI TZ');
+SELECT to_timestamp('2011-12-18 11:38 +01:30', 'YYYY-MM-DD HH12:MI TZ');
+SELECT to_timestamp('2011-12-18 11:38 MSK', 'YYYY-MM-DD HH12:MI TZ');  -- dyntz
+SELECT to_timestamp('2011-12-18 11:38ESTFOO24', 'YYYY-MM-DD HH12:MITZFOOSS');
+SELECT to_timestamp('2011-12-18 11:38-05FOO24', 'YYYY-MM-DD HH12:MITZFOOSS');
+
+SELECT to_timestamp('2011-12-18 11:38 -05', 'YYYY-MM-DD HH12:MI OF');
+SELECT to_timestamp('2011-12-18 11:38 +01:30', 'YYYY-MM-DD HH12:MI OF');

 SELECT to_timestamp('2018-11-02 12:34:56.025', 'YYYY-MM-DD HH24:MI:SS.MS');

@@ -616,6 +624,10 @@ SELECT to_date('2016 366', 'YYYY DDD');  -- ok
 SELECT to_date('2016 367', 'YYYY DDD');
 SELECT to_date('0000-02-01','YYYY-MM-DD');  -- allowed, though it shouldn't be

+-- to_char's TZ format code produces zone abbrev if known
+SELECT to_char('2012-12-12 12:00'::timestamptz, 'YYYY-MM-DD HH:MI:SS TZ');
+SELECT to_char('2012-12-12 12:00'::timestamptz, 'YYYY-MM-DD HH:MI:SS tz');
+
 --
 -- Check behavior with SQL-style fixed-GMT-offset time zone (cf bug #8572)
 --
@@ -632,4 +644,8 @@ SELECT to_char('2012-12-12 12:00'::timestamptz, 'YYYY-MM-DD HH:MI:SS TZ');
 SELECT to_char('2012-12-12 12:00'::timestamptz, 'YYYY-MM-DD SSSS');
 SELECT to_char('2012-12-12 12:00'::timestamptz, 'YYYY-MM-DD SSSSS');

+SET TIME ZONE '+2';
+
+SELECT to_char('2012-12-12 12:00'::timestamptz, 'YYYY-MM-DD HH:MI:SS TZ');
+
 RESET TIME ZONE;
diff --git a/src/test/regress/sql/jsonb_jsonpath.sql b/src/test/regress/sql/jsonb_jsonpath.sql
index e0ce509264..e9bd94b034 100644
--- a/src/test/regress/sql/jsonb_jsonpath.sql
+++ b/src/test/regress/sql/jsonb_jsonpath.sql
@@ -417,6 +417,8 @@ select jsonb_path_query('"2017-03-10t12:34:56+3:10"', '$.datetime()');
 select jsonb_path_query('"2017-03-10 12:34:56.789+3:10"', '$.datetime()');
 select jsonb_path_query('"2017-03-10T12:34:56.789+3:10"', '$.datetime()');
 select jsonb_path_query('"2017-03-10t12:34:56.789+3:10"', '$.datetime()');
+select jsonb_path_query('"2017-03-10T12:34:56.789EST"', '$.datetime()');
+select jsonb_path_query('"2017-03-10T12:34:56.789Z"', '$.datetime()');
 select jsonb_path_query('"12:34:56"', '$.datetime().type()');
 select jsonb_path_query('"12:34:56"', '$.datetime()');
 select jsonb_path_query('"12:34:56+3"', '$.datetime().type()');
--
2.39.3

From e4403f185c4cbd0d5035002a2fe6073acb6c048d Mon Sep 17 00:00:00 2001
From: Tom Lane <tgl@sss.pgh.pa.us>
Date: Tue, 23 Jan 2024 17:25:19 -0500
Subject: [PATCH v2 2/2] Respond to review comments.

Standardize on "FALLTHROUGH" as the way to spell such comments
within formatting.c (there were two of these already).

Improve error message given for a bogus zone abbreviation,
and add some tests to exercise these error cases.
---
 src/backend/utils/adt/formatting.c     | 20 +++++++++++++++++---
 src/test/regress/expected/horology.out |  6 ++++++
 src/test/regress/sql/horology.sql      |  2 ++
 3 files changed, 25 insertions(+), 3 deletions(-)

diff --git a/src/backend/utils/adt/formatting.c b/src/backend/utils/adt/formatting.c
index 8859d174c2..829aaa8d0e 100644
--- a/src/backend/utils/adt/formatting.c
+++ b/src/backend/utils/adt/formatting.c
@@ -3454,7 +3454,7 @@ DCH_from_char(FormatNode *node, const char *in, TmFromChar *out,
             case DCH_FF5:
             case DCH_FF6:
                 out->ff = n->key->id - DCH_FF1 + 1;
-                /* fall through */
+                /* FALLTHROUGH */
             case DCH_US:        /* microsecond */
                 len = from_char_parse_int_len(&out->us, &s,
                                               n->key->id == DCH_US ? 6 :
@@ -3493,9 +3493,23 @@ DCH_from_char(FormatNode *node, const char *in, TmFromChar *out,
                         s += tzlen;
                         break;
                     }
+                    else if (isalpha((unsigned char) *s))
+                    {
+                        /*
+                         * It doesn't match any abbreviation, but it starts
+                         * with a letter.  OF format certainly won't succeed;
+                         * assume it's a misspelled abbreviation and complain
+                         * accordingly.
+                         */
+                        ereturn(escontext,,
+                                (errcode(ERRCODE_INVALID_DATETIME_FORMAT),
+                                 errmsg("invalid value \"%s\" for \"%s\"",
+                                        s, n->key->name),
+                                 errdetail("Time zone abbreviation is not recognized.")));
+                    }
+                    /* otherwise parse it like OF */
                 }
-                /* it doesn't match any abbrev, so parse it like OF */
-                /* FALL THRU */
+                /* FALLTHROUGH */
             case DCH_OF:
                 /* OF is equivalent to TZH or TZH:TZM */
                 /* see TZH comments below */
diff --git a/src/test/regress/expected/horology.out b/src/test/regress/expected/horology.out
index 142a2109ba..c810c4fc91 100644
--- a/src/test/regress/expected/horology.out
+++ b/src/test/regress/expected/horology.out
@@ -3176,6 +3176,12 @@ SELECT to_timestamp('2011-12-18 11:38-05FOO24', 'YYYY-MM-DD HH12:MITZFOOSS');
  Sun Dec 18 08:38:24 2011 PST
 (1 row)

+SELECT to_timestamp('2011-12-18 11:38 JUNK', 'YYYY-MM-DD HH12:MI TZ');  -- error
+ERROR:  invalid value "JUNK" for "TZ"
+DETAIL:  Time zone abbreviation is not recognized.
+SELECT to_timestamp('2011-12-18 11:38 ...', 'YYYY-MM-DD HH12:MI TZ');  -- error
+ERROR:  invalid value ".." for "TZ"
+DETAIL:  Value must be an integer.
 SELECT to_timestamp('2011-12-18 11:38 -05', 'YYYY-MM-DD HH12:MI OF');
          to_timestamp
 ------------------------------
diff --git a/src/test/regress/sql/horology.sql b/src/test/regress/sql/horology.sql
index fe647af976..7edd65facb 100644
--- a/src/test/regress/sql/horology.sql
+++ b/src/test/regress/sql/horology.sql
@@ -507,6 +507,8 @@ SELECT to_timestamp('2011-12-18 11:38 +01:30', 'YYYY-MM-DD HH12:MI TZ');
 SELECT to_timestamp('2011-12-18 11:38 MSK', 'YYYY-MM-DD HH12:MI TZ');  -- dyntz
 SELECT to_timestamp('2011-12-18 11:38ESTFOO24', 'YYYY-MM-DD HH12:MITZFOOSS');
 SELECT to_timestamp('2011-12-18 11:38-05FOO24', 'YYYY-MM-DD HH12:MITZFOOSS');
+SELECT to_timestamp('2011-12-18 11:38 JUNK', 'YYYY-MM-DD HH12:MI TZ');  -- error
+SELECT to_timestamp('2011-12-18 11:38 ...', 'YYYY-MM-DD HH12:MI TZ');  -- error

 SELECT to_timestamp('2011-12-18 11:38 -05', 'YYYY-MM-DD HH12:MI OF');
 SELECT to_timestamp('2011-12-18 11:38 +01:30', 'YYYY-MM-DD HH12:MI OF');
--
2.39.3