From 11b623dd0a2c385719ebbbdd42dd4ec395dcdc9d Mon Sep 17 00:00:00 2001 From: Andrew Dunstan Date: Tue, 9 Jan 2018 14:25:05 -0500 Subject: [PATCH] Implement TZH and TZM timestamp format patterns These are compatible with Oracle and required for the datetime template language for jsonpath in an upcoming patch. Nikita Glukhov and Andrew Dunstan, reviewed by Pavel Stehule. --- doc/src/sgml/func.sgml | 8 +++ src/backend/utils/adt/formatting.c | 69 ++++++++++++++++++++-- src/test/regress/expected/horology.out | 30 ++++++++++ src/test/regress/expected/timestamptz.out | 72 ++++++++++++++--------- src/test/regress/sql/horology.sql | 6 ++ src/test/regress/sql/timestamptz.sql | 20 ++++--- 6 files changed, 164 insertions(+), 41 deletions(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 4dd9d029e69..2428434030b 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -6073,6 +6073,14 @@ SELECT regexp_match('abc01234xyz', '(?:(.*?)(\d+)(.*)){1,1}'); lower case time-zone abbreviation (only supported in to_char) + + TZH + time-zone hours + + + TZM + time-zone minutes + OF time-zone offset from UTC diff --git a/src/backend/utils/adt/formatting.c b/src/backend/utils/adt/formatting.c index 0e30810ae4d..b8bd4caa3e7 100644 --- a/src/backend/utils/adt/formatting.c +++ b/src/backend/utils/adt/formatting.c @@ -424,7 +424,10 @@ typedef struct j, us, yysz, /* is it YY or YYYY ? */ - clock; /* 12 or 24 hour clock? */ + clock, /* 12 or 24 hour clock? */ + tzsign, /* +1, -1 or 0 if timezone info is absent */ + tzh, + tzm; } TmFromChar; #define ZERO_tmfc(_X) memset(_X, 0, sizeof(TmFromChar)) @@ -470,6 +473,7 @@ do { \ (_X)->tm_sec = (_X)->tm_year = (_X)->tm_min = (_X)->tm_wday = \ (_X)->tm_hour = (_X)->tm_yday = (_X)->tm_isdst = 0; \ (_X)->tm_mday = (_X)->tm_mon = 1; \ + (_X)->tm_zone = NULL; \ } while(0) #define ZERO_tmtc(_X) \ @@ -609,6 +613,8 @@ typedef enum DCH_RM, DCH_SSSS, DCH_SS, + DCH_TZH, + DCH_TZM, DCH_TZ, DCH_US, DCH_WW, @@ -756,7 +762,9 @@ static const KeyWord DCH_keywords[] = { {"RM", 2, DCH_RM, false, FROM_CHAR_DATE_GREGORIAN}, /* R */ {"SSSS", 4, DCH_SSSS, true, FROM_CHAR_DATE_NONE}, /* S */ {"SS", 2, DCH_SS, true, FROM_CHAR_DATE_NONE}, - {"TZ", 2, DCH_TZ, false, FROM_CHAR_DATE_NONE}, /* T */ + {"TZH", 3, DCH_TZH, false, FROM_CHAR_DATE_NONE}, /* T */ + {"TZM", 3, DCH_TZM, true, FROM_CHAR_DATE_NONE}, + {"TZ", 2, DCH_TZ, false, FROM_CHAR_DATE_NONE}, {"US", 2, DCH_US, true, FROM_CHAR_DATE_NONE}, /* U */ {"WW", 2, DCH_WW, true, FROM_CHAR_DATE_GREGORIAN}, /* W */ {"W", 1, DCH_W, true, FROM_CHAR_DATE_GREGORIAN}, @@ -879,7 +887,7 @@ static const int DCH_index[KeyWord_INDEX_SIZE] = { -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, DCH_A_D, DCH_B_C, DCH_CC, DCH_DAY, -1, DCH_FX, -1, DCH_HH24, DCH_IDDD, DCH_J, -1, -1, DCH_MI, -1, DCH_OF, - DCH_P_M, DCH_Q, DCH_RM, DCH_SSSS, DCH_TZ, DCH_US, -1, DCH_WW, -1, DCH_Y_YYY, + DCH_P_M, DCH_Q, DCH_RM, DCH_SSSS, DCH_TZH, DCH_US, -1, DCH_WW, -1, DCH_Y_YYY, -1, -1, -1, -1, -1, -1, -1, DCH_a_d, DCH_b_c, DCH_cc, DCH_day, -1, DCH_fx, -1, DCH_hh24, DCH_iddd, DCH_j, -1, -1, DCH_mi, -1, -1, DCH_p_m, DCH_q, DCH_rm, DCH_ssss, DCH_tz, DCH_us, -1, DCH_ww, @@ -2519,6 +2527,19 @@ DCH_to_char(FormatNode *node, bool is_interval, TmToChar *in, char *out, Oid col s += strlen(s); } break; + case DCH_TZH: + INVALID_FOR_INTERVAL; + sprintf(s, "%c%02d", + (tm->tm_gmtoff >= 0) ? '+' : '-', + abs((int) tm->tm_gmtoff) / SECS_PER_HOUR); + s += strlen(s); + break; + case DCH_TZM: + INVALID_FOR_INTERVAL; + sprintf(s, "%02d", + (abs((int) tm->tm_gmtoff) % SECS_PER_HOUR) / SECS_PER_MINUTE); + s += strlen(s); + break; case DCH_OF: INVALID_FOR_INTERVAL; sprintf(s, "%c%0*d", @@ -3070,6 +3091,20 @@ DCH_from_char(FormatNode *node, char *in, TmFromChar *out) errmsg("formatting field \"%s\" is only supported in to_char", n->key->name))); break; + case DCH_TZH: + out->tzsign = *s == '-' ? -1 : +1; + + if (*s == '+' || *s == '-' || *s == ' ') + s++; + + from_char_parse_int_len(&out->tzh, &s, 2, n); + break; + case DCH_TZM: + /* assign positive timezone sign if TZH was not seen before */ + if (!out->tzsign) + out->tzsign = +1; + from_char_parse_int_len(&out->tzm, &s, 2, n); + break; case DCH_A_D: case DCH_B_C: case DCH_a_d: @@ -3536,7 +3571,16 @@ to_timestamp(PG_FUNCTION_ARGS) do_to_timestamp(date_txt, fmt, &tm, &fsec); - tz = DetermineTimeZoneOffset(&tm, session_timezone); + /* Use the specified time zone, if any. */ + if (tm.tm_zone) + { + int dterr = DecodeTimezone((char *) tm.tm_zone, &tz); + + if (dterr) + DateTimeParseError(dterr, text_to_cstring(date_txt), "timestamptz"); + } + else + tz = DetermineTimeZoneOffset(&tm, session_timezone); if (tm2timestamp(&tm, fsec, &tz, &result) != 0) ereport(ERROR, @@ -3858,6 +3902,23 @@ do_to_timestamp(text *date_txt, text *fmt, *fsec < INT64CONST(0) || *fsec >= USECS_PER_SEC) DateTimeParseError(DTERR_FIELD_OVERFLOW, date_str, "timestamp"); + /* Save parsed time-zone into tm->tm_zone if it was specified */ + if (tmfc.tzsign) + { + char *tz; + + if (tmfc.tzh < 0 || tmfc.tzh > MAX_TZDISP_HOUR || + tmfc.tzm < 0 || tmfc.tzm >= MINS_PER_HOUR) + DateTimeParseError(DTERR_TZDISP_OVERFLOW, date_str, "timestamp"); + + tz = palloc(7); + + snprintf(tz, 7, "%c%02d:%02d", + tmfc.tzsign > 0 ? '+' : '-', tmfc.tzh, tmfc.tzm); + + tm->tm_zone = tz; + } + DEBUG_TM(tm); pfree(date_str); diff --git a/src/test/regress/expected/horology.out b/src/test/regress/expected/horology.out index 7b3d058425d..63e39198e68 100644 --- a/src/test/regress/expected/horology.out +++ b/src/test/regress/expected/horology.out @@ -2930,6 +2930,36 @@ SELECT to_timestamp('2011-12-18 11:38 PM', 'YYYY-MM-DD HH12:MI PM'); Sun Dec 18 23:38:00 2011 PST (1 row) +SELECT to_timestamp('2011-12-18 11:38 +05', 'YYYY-MM-DD HH12:MI TZH'); + to_timestamp +------------------------------ + Sat Dec 17 22:38:00 2011 PST +(1 row) + +SELECT to_timestamp('2011-12-18 11:38 -05', 'YYYY-MM-DD HH12:MI TZH'); + to_timestamp +------------------------------ + Sun Dec 18 08:38:00 2011 PST +(1 row) + +SELECT to_timestamp('2011-12-18 11:38 +05:20', 'YYYY-MM-DD HH12:MI TZH:TZM'); + to_timestamp +------------------------------ + Sat Dec 17 22:18:00 2011 PST +(1 row) + +SELECT to_timestamp('2011-12-18 11:38 -05:20', 'YYYY-MM-DD HH12:MI TZH:TZM'); + to_timestamp +------------------------------ + Sun Dec 18 08:58:00 2011 PST +(1 row) + +SELECT to_timestamp('2011-12-18 11:38 20', 'YYYY-MM-DD HH12:MI TZM'); + to_timestamp +------------------------------ + Sun Dec 18 03:18:00 2011 PST +(1 row) + -- -- Check handling of multiple spaces in format and/or input -- diff --git a/src/test/regress/expected/timestamptz.out b/src/test/regress/expected/timestamptz.out index 72266709627..a901fd909d3 100644 --- a/src/test/regress/expected/timestamptz.out +++ b/src/test/regress/expected/timestamptz.out @@ -1699,54 +1699,68 @@ SELECT '' AS to_char_11, to_char(d1, 'FMIYYY FMIYY FMIY FMI FMIW FMIDDD FMID') | 2001 1 1 1 1 1 1 (66 rows) --- Check OF with various zone offsets, particularly fractional hours +-- Check OF, TZH, TZM with various zone offsets, particularly fractional hours SET timezone = '00:00'; -SELECT to_char(now(), 'OF'); - to_char ---------- - +00 +SELECT to_char(now(), 'OF') as "OF", to_char(now(), 'TZH:TZM') as "TZH:TZM"; + OF | TZH:TZM +-----+--------- + +00 | +00:00 (1 row) SET timezone = '+02:00'; -SELECT to_char(now(), 'OF'); - to_char ---------- - -02 +SELECT to_char(now(), 'OF') as "OF", to_char(now(), 'TZH:TZM') as "TZH:TZM"; + OF | TZH:TZM +-----+--------- + -02 | -02:00 (1 row) SET timezone = '-13:00'; -SELECT to_char(now(), 'OF'); - to_char ---------- - +13 +SELECT to_char(now(), 'OF') as "OF", to_char(now(), 'TZH:TZM') as "TZH:TZM"; + OF | TZH:TZM +-----+--------- + +13 | +13:00 (1 row) SET timezone = '-00:30'; -SELECT to_char(now(), 'OF'); - to_char ---------- - +00:30 +SELECT to_char(now(), 'OF') as "OF", to_char(now(), 'TZH:TZM') as "TZH:TZM"; + OF | TZH:TZM +--------+--------- + +00:30 | +00:30 (1 row) SET timezone = '00:30'; -SELECT to_char(now(), 'OF'); - to_char ---------- - -00:30 +SELECT to_char(now(), 'OF') as "OF", to_char(now(), 'TZH:TZM') as "TZH:TZM"; + OF | TZH:TZM +--------+--------- + -00:30 | -00:30 (1 row) SET timezone = '-04:30'; -SELECT to_char(now(), 'OF'); - to_char ---------- - +04:30 +SELECT to_char(now(), 'OF') as "OF", to_char(now(), 'TZH:TZM') as "TZH:TZM"; + OF | TZH:TZM +--------+--------- + +04:30 | +04:30 (1 row) SET timezone = '04:30'; -SELECT to_char(now(), 'OF'); - to_char ---------- - -04:30 +SELECT to_char(now(), 'OF') as "OF", to_char(now(), 'TZH:TZM') as "TZH:TZM"; + OF | TZH:TZM +--------+--------- + -04:30 | -04:30 +(1 row) + +SET timezone = '-04:15'; +SELECT to_char(now(), 'OF') as "OF", to_char(now(), 'TZH:TZM') as "TZH:TZM"; + OF | TZH:TZM +--------+--------- + +04:15 | +04:15 +(1 row) + +SET timezone = '04:15'; +SELECT to_char(now(), 'OF') as "OF", to_char(now(), 'TZH:TZM') as "TZH:TZM"; + OF | TZH:TZM +--------+--------- + -04:15 | -04:15 (1 row) RESET timezone; diff --git a/src/test/regress/sql/horology.sql b/src/test/regress/sql/horology.sql index a7bc9dcfc4f..ebb196a1cfc 100644 --- a/src/test/regress/sql/horology.sql +++ b/src/test/regress/sql/horology.sql @@ -446,6 +446,12 @@ SELECT to_timestamp(' 20050302', 'YYYYMMDD'); SELECT to_timestamp('2011-12-18 11:38 AM', 'YYYY-MM-DD HH12:MI PM'); SELECT to_timestamp('2011-12-18 11:38 PM', 'YYYY-MM-DD HH12:MI PM'); +SELECT to_timestamp('2011-12-18 11:38 +05', 'YYYY-MM-DD HH12:MI TZH'); +SELECT to_timestamp('2011-12-18 11:38 -05', 'YYYY-MM-DD HH12:MI TZH'); +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'); + -- -- Check handling of multiple spaces in format and/or input -- diff --git a/src/test/regress/sql/timestamptz.sql b/src/test/regress/sql/timestamptz.sql index 97e57a2403a..f17d153fccb 100644 --- a/src/test/regress/sql/timestamptz.sql +++ b/src/test/regress/sql/timestamptz.sql @@ -248,21 +248,25 @@ SELECT '' AS to_char_10, to_char(d1, 'IYYY IYY IY I IW IDDD ID') SELECT '' AS to_char_11, to_char(d1, 'FMIYYY FMIYY FMIY FMI FMIW FMIDDD FMID') FROM TIMESTAMPTZ_TBL; --- Check OF with various zone offsets, particularly fractional hours +-- Check OF, TZH, TZM with various zone offsets, particularly fractional hours SET timezone = '00:00'; -SELECT to_char(now(), 'OF'); +SELECT to_char(now(), 'OF') as "OF", to_char(now(), 'TZH:TZM') as "TZH:TZM"; SET timezone = '+02:00'; -SELECT to_char(now(), 'OF'); +SELECT to_char(now(), 'OF') as "OF", to_char(now(), 'TZH:TZM') as "TZH:TZM"; SET timezone = '-13:00'; -SELECT to_char(now(), 'OF'); +SELECT to_char(now(), 'OF') as "OF", to_char(now(), 'TZH:TZM') as "TZH:TZM"; SET timezone = '-00:30'; -SELECT to_char(now(), 'OF'); +SELECT to_char(now(), 'OF') as "OF", to_char(now(), 'TZH:TZM') as "TZH:TZM"; SET timezone = '00:30'; -SELECT to_char(now(), 'OF'); +SELECT to_char(now(), 'OF') as "OF", to_char(now(), 'TZH:TZM') as "TZH:TZM"; SET timezone = '-04:30'; -SELECT to_char(now(), 'OF'); +SELECT to_char(now(), 'OF') as "OF", to_char(now(), 'TZH:TZM') as "TZH:TZM"; SET timezone = '04:30'; -SELECT to_char(now(), 'OF'); +SELECT to_char(now(), 'OF') as "OF", to_char(now(), 'TZH:TZM') as "TZH:TZM"; +SET timezone = '-04:15'; +SELECT to_char(now(), 'OF') as "OF", to_char(now(), 'TZH:TZM') as "TZH:TZM"; +SET timezone = '04:15'; +SELECT to_char(now(), 'OF') as "OF", to_char(now(), 'TZH:TZM') as "TZH:TZM"; RESET timezone; CREATE TABLE TIMESTAMPTZ_TST (a int , b timestamptz); -- 2.30.2