On 01/03/2018 02:21 PM, Andrew Dunstan wrote:
>
> On 01/03/2018 01:34 PM, Tom Lane wrote:
>> Andrew Dunstan <andrew.duns...@2ndquadrant.com> writes:
>>> This small and simple standalone patch extracted from the SQL/JSON work
>>> would allow the user to supply a string with a time zone specified as
>>> hh:mm thus:
>>> 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
>> I see that Oracle's to_timestamp supports these format codes, so +1
>> if you've checked that the behavior is compatible with Oracle. The
>> most obvious possible gotcha is whether + is east or west of GMT,
>> but also there's formatting questions like what the field width is
>> and whether leading zeroes are printed.
>>
>> Also, I'm unimpressed that you've not bothered to implement the
>> to_char direction. That moves this from a feature addition to
>> a kluge, IMO, especially since that ought to be the easier direction.
>>
>>
>
>
> To be clear, this isn't my patch, it one I extracted from the large
> patchset Nikita Glukhov posted for SQL/JSON, in order to kickstart
> process there.
>
> I wasn't aware of the Oracle implementation.
>
> I agree that supporting these in to_char would be useful, and should not
> be terribly difficult.
>
>
Here is a version that adds the to_char direction. AFAICT it is
compatible with Oracle.
cheers
andrew
--
Andrew Dunstan https://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 4dd9d02..2428434 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -6074,6 +6074,14 @@ SELECT regexp_match('abc01234xyz', '(?:(.*?)(\d+)(.*)){1,1}');
(only supported in <function>to_char</function>)</entry>
</row>
<row>
+ <entry><literal>TZH</literal></entry>
+ <entry>time-zone hours</entry>
+ </row>
+ <row>
+ <entry><literal>TZM</literal></entry>
+ <entry>time-zone minutes</entry>
+ </row>
+ <row>
<entry><literal>OF</literal></entry>
<entry>time-zone offset from UTC
(only supported in <function>to_char</function>)</entry>
diff --git a/src/backend/utils/adt/formatting.c b/src/backend/utils/adt/formatting.c
index 0e30810..b8bd4ca 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 7b3d058..63e3919 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 7226670..a901fd9 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 a7bc9dc..ebb196a 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 97e57a2..f17d153 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);
