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
The patch seems pretty straightforward to me, and it's required for the
jsonpath patches which would be the next cab off the rank in the
SQL/JSON work.
It seems like something worth having quite independently of the SQL/JSON
stuff anyway.
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..3978747 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -6074,6 +6074,16 @@ 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
+ (only supported in <function>to_timestamp</function>)</entry>
+ </row>
+ <row>
+ <entry><literal>TZM</literal></entry>
+ <entry>time-zone minutes
+ (only supported in <function>to_timestamp</function>)</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 ec97de0..e7ca249 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,13 @@ DCH_to_char(FormatNode *node, bool is_interval, TmToChar *in, char *out, Oid col
s += strlen(s);
}
break;
+ case DCH_TZH:
+ case DCH_TZM:
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("formatting field \"%s\" is only supported in "
+ "to_timestamp", n->key->name)));
+ break;
case DCH_OF:
INVALID_FOR_INTERVAL;
sprintf(s, "%c%0*d",
@@ -3070,6 +3085,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 +3565,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 +3896,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/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
--
