to_timestamp TZH and TZM format specifiers

Started by Andrew Dunstanabout 8 years ago15 messages

andrew.dunstan@2ndquadrant.com

about 8 years ago

1 attachment(s)

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

Attachments:

0001-add-TZH-and-TZM-datetime-template-patterns-v06.patchtext/x-patch; name=0001-add-TZH-and-TZM-datetime-template-patterns-v06.patchDownload

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
 --

Tom Lane

tgl@sss.pgh.pa.us

about 8 years ago

In reply to: Andrew Dunstan (#1)

Re: to_timestamp TZH and TZM format specifiers

Andrew Dunstan <andrew.dunstan@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.

BTW, I had not known this before, but according to the page I'm
looking at

https://docs.oracle.com/database/121/SQLRF/sql_elements004.htm#SQLRF00212

Oracle also supports "TZD" to mean a time zone abbreviation (their
example is "PDT") and "TZR" to mean a time zone name (their example
is "US/Pacific", so yes they mean the IANA zone names). Those seem
remarkably useful, so I'm surprised we've not added support for them.

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.

I'm quite confused as to why a patch that alleges to be implementing
SQL-standard behavior would be depending on an Oracle-ism. That's
not an argument against this patch, but it is a question about the
SQL/JSON work.

regards, tom lane

Andrew Dunstan

andrew.dunstan@2ndquadrant.com

about 8 years ago

In reply to: Tom Lane (#2)

Re: to_timestamp TZH and TZM format specifiers

On 01/03/2018 01:34 PM, Tom Lane wrote:

Andrew Dunstan <andrew.dunstan@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.

BTW, I had not known this before, but according to the page I'm
looking at

https://docs.oracle.com/database/121/SQLRF/sql_elements004.htm#SQLRF00212

Oracle also supports "TZD" to mean a time zone abbreviation (their
example is "PDT") and "TZR" to mean a time zone name (their example
is "US/Pacific", so yes they mean the IANA zone names). Those seem
remarkably useful, so I'm surprised we've not added support for them.

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.

I also agree that TZD and TZR would be very useful, but perhaps they
could be done in a separate patch.

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.

I'm quite confused as to why a patch that alleges to be implementing
SQL-standard behavior would be depending on an Oracle-ism. That's
not an argument against this patch, but it is a question about the
SQL/JSON work.

My understanding is that the standard specifies TZH and TZM as part of
its json datetime template language. It doesn't appear to specify TZD or
TZR.

cheers

andrew

--
Andrew Dunstan https://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Tom Lane

tgl@sss.pgh.pa.us

about 8 years ago

In reply to: Andrew Dunstan (#3)

Re: to_timestamp TZH and TZM format specifiers

Andrew Dunstan <andrew.dunstan@2ndquadrant.com> writes:

On 01/03/2018 01:34 PM, Tom Lane wrote:

BTW, I had not known this before, but according to the page I'm
looking at
https://docs.oracle.com/database/121/SQLRF/sql_elements004.htm#SQLRF00212
Oracle also supports "TZD" to mean a time zone abbreviation (their
example is "PDT") and "TZR" to mean a time zone name (their example
is "US/Pacific", so yes they mean the IANA zone names). Those seem
remarkably useful, so I'm surprised we've not added support for them.

I also agree that TZD and TZR would be very useful, but perhaps they
could be done in a separate patch.

Yes, of course, I did not mean to imply that this patch needs to include
them. I was just surprised because I'd always believed there was no such
facility in Oracle. I wonder how long ago they added those ...

regards, tom lane

Vik Fearing

vik.fearing@2ndquadrant.com

about 8 years ago

In reply to: Andrew Dunstan (#1)

Re: to_timestamp TZH and TZM format specifiers

On 01/03/2018 07:03 PM, Andrew Dunstan wrote:

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.

Shouldn't this support TZS or something for seconds?
--
Vik Fearing +33 6 46 75 15 36
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

Tom Lane

tgl@sss.pgh.pa.us

about 8 years ago

In reply to: Vik Fearing (#5)

Re: to_timestamp TZH and TZM format specifiers

Vik Fearing <vik.fearing@2ndquadrant.com> writes:

Shouldn't this support TZS or something for seconds?

According to the docs I cited upthread, there's no such field type
in Oracle.

regards, tom lane

Vik Fearing

vik.fearing@2ndquadrant.com

about 8 years ago

In reply to: Tom Lane (#6)

Re: to_timestamp TZH and TZM format specifiers

On 01/03/2018 08:39 PM, Tom Lane wrote:

Vik Fearing <vik.fearing@2ndquadrant.com> writes:

Shouldn't this support TZS or something for seconds?

According to the docs I cited upthread, there's no such field type
in Oracle.

So what?
--
Vik Fearing +33 6 46 75 15 36
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

Tom Lane

tgl@sss.pgh.pa.us

about 8 years ago

In reply to: Vik Fearing (#7)

Re: to_timestamp TZH and TZM format specifiers

Vik Fearing <vik.fearing@2ndquadrant.com> writes:

On 01/03/2018 08:39 PM, Tom Lane wrote:

Vik Fearing <vik.fearing@2ndquadrant.com> writes:

Shouldn't this support TZS or something for seconds?

According to the docs I cited upthread, there's no such field type
in Oracle.

So what?

Well, the function is meant to be Oracle-compatible, so where's the
use case?

I'm pretty sure that it's been years since any standard time zones
weren't an integral number of minutes off UTC, which may explain
why Oracle hasn't felt the need for this. I notice that the SQL
standard itself also believes that timezone offsets must be an
integral number of minutes.

regards, tom lane

Andrew Dunstan

andrew.dunstan@2ndquadrant.com

about 8 years ago

In reply to: Andrew Dunstan (#3)

1 attachment(s)

Re: to_timestamp TZH and TZM format specifiers

On 01/03/2018 02:21 PM, Andrew Dunstan wrote:

On 01/03/2018 01:34 PM, Tom Lane wrote:

Andrew Dunstan <andrew.dunstan@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

Attachments:

tzhtzm-amd-v1.patchtext/x-patch; name=tzhtzm-amd-v1.patchDownload

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

#10

Nikita Glukhov

n.gluhov@postgrespro.ru

about 8 years ago

In reply to: Tom Lane (#2)

Re: to_timestamp TZH and TZM format specifiers

On 03.01.2018 21:34, Tom Lane wrote:

Andrew Dunstan <andrew.dunstan@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.

BTW, I had not known this before, but according to the page I'm
looking at

https://docs.oracle.com/database/121/SQLRF/sql_elements004.htm#SQLRF00212

Oracle also supports "TZD" to mean a time zone abbreviation (their
example is "PDT") and "TZR" to mean a time zone name (their example
is "US/Pacific", so yes they mean the IANA zone names). Those seem
remarkably useful, so I'm surprised we've not added support for them.

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.

I'm quite confused as to why a patch that alleges to be implementing
SQL-standard behavior would be depending on an Oracle-ism. That's
not an argument against this patch, but it is a question about the
SQL/JSON work.

regards, tom lane

TZH and TZM specifiers are required by standard for SQL/JSON item method
.datetime() (Feature F411, “Time zone specification”). To be fully
compliant, we should also support RR, RRRR and FF1-FF9 specifiers.

.datetime() item method is used for conversion of JSON string items to
SQL/JSON datetime items. Its optional argument "format" determines
target datetime type:

=# SELECT jsonb '"10-03-2017 12:34 +05:20"' @* '$.datetime("DD-MM-YYYY").type()';
?column?
----------
"date"
(1 row)

=# SELECT jsonb '"10-03-2017 12:34 +05:20"' @* '$.datetime("DD-MM-YYYY HH24:MI").type()';
?column?
-------------------------------
"timestamp without time zone"
(1 row)

=# SELECT jsonb '"10-03-2017 12:34 +05:20"' @* '$.datetime("DD-MM-YYYY HH24:MI TZH:TZM").type()';
?column?
----------------------------
"timestamp with time zone"
(1 row)

-- automatic datetime type recognition for ISO-formatted strings
=# SELECT jsonb '"2017-10-03 12:34:56 +05:20"' @* '$.datetime().type()';
?column?
----------------------------
"timestamp with time zone"
(1 row)

Here are corresponding excerpts from the SQL-2016 standard:

9.44 Datetime templates

<datetime template> ::=
{ <datetime template part> }...

<datetime template year> ::= YYYY | YYY | YY | Y
<datetime template rounded year> ::= RRRR | RR
<datetime template month> ::= MM
<datetime template day of month> ::= DD
<datetime template day of year> ::= DDD
<datetime template 12-hour> ::= HH | HH12
<datetime template 24-hour> ::= HH24
<datetime template minute> ::= MI
<datetime template second of minute> ::= SS
<datetime template second of day> ::= SSSSS
<datetime template fraction> ::=
FF1 | FF2 | FF3 | FF4 | FF5 | FF6 | FF7 | FF8 | FF9
<datetime template am/pm> ::= A.M. | P.M.
<datetime template time zone hour> ::= TZH
<datetime template time zone minute> ::= TZM

9.39 SQL/JSON path language: syntax and semantics
...
10) If <JSON datetime template> JDT is specified, then the value of JDT shall
conform to the lexical grammar of a <datetime template> in the Format of
Subclause 9.44, “Datetime templates”.

a) If JDT contains
<datetime template year>,
<datetime template rounded year>,
<datetime template month>,
<datetime template day of month>, or
<datetime template day of year>,
then JDT is dated.

b) If JDT contains
<datetime template 12-hour>,
<datetime template 24-hour>,
<datetime template minute>,
<datetime template second of minute>,
<datetime template second of day>,
<datetime template fraction>, or
<datetime template am/pm>,
then JDT is timed.

The fractional seconds precision FSP of JDT is
Case:

i) If JDT contains <datetime template fraction>
FF1, FF2, FF3, FF4, FF5, FF6, FF7, FF8, or FF9,
then 1 (one), 2, 3, 4, 5, 6, 7, 8, or 9, respectively.

ii) Otherwise, 0 (zero).

c) If JDT contains
<datetime template time zone hour> or
<datetime template time zone minute>,
then JDT is zoned.

d) If JDT is zoned, then JDT shall be timed.

e) JDT shall be dated or timed or both.

f) The implicit datetime data type IDT of JDT is
Case:
i) If JDT is dated, timed, and zoned, then TIMESTAMP (FSP) WITH TIME ZONE.
ii) If JDT is dated, timed, and not zoned, then
TIMESTAMP (FSP) WITHOUT TIME ZONE.
iii) If JDT is timed and zoned, then TIME (FSP) WITH TIME ZONE.
iv) If JDT is timed and not zoned, then TIME (FSP) WITHOUT TIME ZONE.
v) If JDT is dated but not timed and not zoned, then DATE.
...

(RR/RRRR specifiers explanation)

9.43 Converting a formatted character string to a datetime
...
5) Let NOW be the value of CURRENT_TIMESTAMP.
Let CY be the YEAR field of NOW.
Let CYLIT be an <unsigned integer> of four <digit>s whose value is CY.
Let CM be the MONTH field of NOW.
Let CMLIT be an <unsigned integer> of two <digit>s whose value is CM.

6) Case:
a) If CT contains a <datetime template year> YY, then:
i) Let YYPOS be an <exact numeric literal> whose value is the regular
expression position of YY.
ii) Let YYSTR be the result of SUBSTRING_REGEX ( RX IN FCS GROUP YYPOS )
iii) Let YYLEN be the length of YYSTR.
iv) Let YYPREFIX be the first (4 – YYLEN) digits of CYLIT.
NOTE 471 — If the length of YYSTR is 4, then YYPREFIX is a
zero-length string.
v) Let YYYY be the result of YYPREFIX || YYSTR
vi) Let YEAR be the value of YYYY interpreted as an <unsigned integer>.

b) If CT contains a <datetime template rounded year> RR, then:
i) Let RRPOS be an <exact numeric literal> whose value is the regular
expression position of RR.
ii) Let RRSTR be the result of SUBSTRING_REGEX ( RX IN FCS GROUP RRPOS )
iii) Let RRLEN be the length of RRSTR.
iv) Let RY be an implementation-defined exact numeric value of scale 0
(zero) that is between CY–100 and CY+100, inclusive. Let RYLIT be
an <unsigned integer> of four <digit>s whose value is RY.
v) Let RRPREFIX be the first (4 – RRLEN) digits of RYLIT.
NOTE 472 — If the length of RRSTR is 4, then RRPREFIX is a zero-length
string.
vi) Let RRRR be the result of RRPREFIX || RRSTR
vii) Let YEAR be the value of RRRR interpreted as an <unsigned integer>.
c) Otherwise, let YEAR be CY.

--
Nikita Glukhov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

#11

Pavel Stehule

pavel.stehule@gmail.com

about 8 years ago

In reply to: Nikita Glukhov (#10)

Re: to_timestamp TZH and TZM format specifiers

2018-01-08 1:22 GMT+01:00 Nikita Glukhov <n.gluhov@postgrespro.ru>:

On 03.01.2018 21:34, Tom Lane wrote:

Andrew Dunstan <andrew.dunstan@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.

BTW, I had not known this before, but according to the page I'm
looking at

https://docs.oracle.com/database/121/SQLRF/sql_elements004.htm#SQLRF00212

Oracle also supports "TZD" to mean a time zone abbreviation (their
example is "PDT") and "TZR" to mean a time zone name (their example
is "US/Pacific", so yes they mean the IANA zone names). Those seem
remarkably useful, so I'm surprised we've not added support for them.

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.

I'm quite confused as to why a patch that alleges to be implementing
SQL-standard behavior would be depending on an Oracle-ism. That's
not an argument against this patch, but it is a question about the
SQL/JSON work.

regards, tom lane

TZH and TZM specifiers are required by standard for SQL/JSON item method
.datetime() (Feature F411, “Time zone specification”). To be fully
compliant, we should also support RR, RRRR and FF1-FF9 specifiers.

.datetime() item method is used for conversion of JSON string items to
SQL/JSON datetime items. Its optional argument "format" determines
target datetime type:

=# SELECT jsonb '"10-03-2017 12:34 +05:20"' @*
'$.datetime("DD-MM-YYYY").type()';
?column?
----------
"date"
(1 row)

=# SELECT jsonb '"10-03-2017 12:34 +05:20"' @* '$.datetime("DD-MM-YYYY
HH24:MI").type()';
?column?
-------------------------------
"timestamp without time zone"
(1 row)

=# SELECT jsonb '"10-03-2017 12:34 +05:20"' @* '$.datetime("DD-MM-YYYY
HH24:MI TZH:TZM").type()';
?column?
----------------------------
"timestamp with time zone"
(1 row)

-- automatic datetime type recognition for ISO-formatted strings
=# SELECT jsonb '"2017-10-03 12:34:56 +05:20"' @* '$.datetime().type()';
?column?
----------------------------
"timestamp with time zone"
(1 row)

Here are corresponding excerpts from the SQL-2016 standard:

9.44 Datetime templates

<datetime template> ::=
{ <datetime template part> }...

<datetime template part> ::=
<datetime template field>
| <datetime template delimiter>

<datetime template field> ::=
<datetime template year>
| <datetime template rounded year>
| <datetime template month>
| <datetime template day of month>
| <datetime template day of year>
| <datetime template 12-hour>
| <datetime template 24-hour>
| <datetime template minute>
| <datetime template second of minute>
| <datetime template second of day>
| <datetime template fraction>
| <datetime template am/pm>
| <datetime template time zone hour>
| <datetime template time zone minute>

<datetime template delimiter> ::=
<minus sign>
| <period>
| <solidus>
| <comma>
| <apostrophe>
| <semicolon>
| <colon>
| <space>

<datetime template year> ::= YYYY | YYY | YY | Y
<datetime template rounded year> ::= RRRR | RR
<datetime template month> ::= MM
<datetime template day of month> ::= DD
<datetime template day of year> ::= DDD
<datetime template 12-hour> ::= HH | HH12
<datetime template 24-hour> ::= HH24
<datetime template minute> ::= MI
<datetime template second of minute> ::= SS
<datetime template second of day> ::= SSSSS
<datetime template fraction> ::=
FF1 | FF2 | FF3 | FF4 | FF5 | FF6 | FF7 | FF8 | FF9
<datetime template am/pm> ::= A.M. | P.M.
<datetime template time zone hour> ::= TZH
<datetime template time zone minute> ::= TZM

9.39 SQL/JSON path language: syntax and semantics
...
10) If <JSON datetime template> JDT is specified, then the value of JDT
shall
conform to the lexical grammar of a <datetime template> in the Format of
Subclause 9.44, “Datetime templates”.

a) If JDT contains
<datetime template year>,
<datetime template rounded year>,
<datetime template month>,
<datetime template day of month>, or
<datetime template day of year>,
then JDT is dated.

b) If JDT contains
<datetime template 12-hour>,
<datetime template 24-hour>,
<datetime template minute>,
<datetime template second of minute>,
<datetime template second of day>,
<datetime template fraction>, or
<datetime template am/pm>,
then JDT is timed.

The fractional seconds precision FSP of JDT is
Case:

i) If JDT contains <datetime template fraction>
FF1, FF2, FF3, FF4, FF5, FF6, FF7, FF8, or FF9,
then 1 (one), 2, 3, 4, 5, 6, 7, 8, or 9, respectively.

ii) Otherwise, 0 (zero).

c) If JDT contains
<datetime template time zone hour> or
<datetime template time zone minute>,
then JDT is zoned.

d) If JDT is zoned, then JDT shall be timed.

e) JDT shall be dated or timed or both.
f) The implicit datetime data type IDT of JDT is
Case:
i) If JDT is dated, timed, and zoned, then TIMESTAMP (FSP) WITH TIME
ZONE.
ii) If JDT is dated, timed, and not zoned, then
TIMESTAMP (FSP) WITHOUT TIME ZONE.
iii) If JDT is timed and zoned, then TIME (FSP) WITH TIME ZONE.
iv) If JDT is timed and not zoned, then TIME (FSP) WITHOUT TIME ZONE.
v) If JDT is dated but not timed and not zoned, then DATE.
...

(RR/RRRR specifiers explanation)

9.43 Converting a formatted character string to a datetime
...
5) Let NOW be the value of CURRENT_TIMESTAMP.
Let CY be the YEAR field of NOW.
Let CYLIT be an <unsigned integer> of four <digit>s whose value is CY.
Let CM be the MONTH field of NOW.
Let CMLIT be an <unsigned integer> of two <digit>s whose value is CM.

6) Case:
a) If CT contains a <datetime template year> YY, then:
i) Let YYPOS be an <exact numeric literal> whose value is the
regular
expression position of YY.
ii) Let YYSTR be the result of SUBSTRING_REGEX ( RX IN FCS GROUP
YYPOS )
iii) Let YYLEN be the length of YYSTR.
iv) Let YYPREFIX be the first (4 – YYLEN) digits of CYLIT.
NOTE 471 — If the length of YYSTR is 4, then YYPREFIX is a
zero-length string.
v) Let YYYY be the result of YYPREFIX || YYSTR
vi) Let YEAR be the value of YYYY interpreted as an <unsigned
integer>.

b) If CT contains a <datetime template rounded year> RR, then:
i) Let RRPOS be an <exact numeric literal> whose value is the
regular
expression position of RR.
ii) Let RRSTR be the result of SUBSTRING_REGEX ( RX IN FCS GROUP
RRPOS )
iii) Let RRLEN be the length of RRSTR.
iv) Let RY be an implementation-defined exact numeric value of
scale 0
(zero) that is between CY–100 and CY+100, inclusive. Let RYLIT
be
an <unsigned integer> of four <digit>s whose value is RY.
v) Let RRPREFIX be the first (4 – RRLEN) digits of RYLIT.
NOTE 472 — If the length of RRSTR is 4, then RRPREFIX is a
zero-length
string.
vi) Let RRRR be the result of RRPREFIX || RRSTR
vii) Let YEAR be the value of RRRR interpreted as an <unsigned
integer>.
c) Otherwise, let YEAR be CY.

I checked this patch and I think so it is correct.

1. all tests passed
2. no problems with patching and compilation
3. the doc is good enough
4. I can confirm so Oracle 12c supports these formats, but I have not
possibility to test it
5. the behave is consistent with timestamp with time zone

I'll mark this patch as ready for commiter

Regards

Pavel

Show quoted text

--
Nikita Glukhov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

#12

Pavel Stehule

pavel.stehule@gmail.com

about 8 years ago

In reply to: Pavel Stehule (#11)

Re: to_timestamp TZH and TZM format specifiers

2018-01-09 19:46 GMT+01:00 Pavel Stehule <pavel.stehule@gmail.com>:

Hi

2018-01-08 1:22 GMT+01:00 Nikita Glukhov <n.gluhov@postgrespro.ru>:

On 03.01.2018 21:34, Tom Lane wrote:

Andrew Dunstan <andrew.dunstan@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.

BTW, I had not known this before, but according to the page I'm
looking at

https://docs.oracle.com/database/121/SQLRF/sql_elements004.h
tm#SQLRF00212

Oracle also supports "TZD" to mean a time zone abbreviation (their
example is "PDT") and "TZR" to mean a time zone name (their example
is "US/Pacific", so yes they mean the IANA zone names). Those seem
remarkably useful, so I'm surprised we've not added support for them.

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.

I'm quite confused as to why a patch that alleges to be implementing
SQL-standard behavior would be depending on an Oracle-ism. That's
not an argument against this patch, but it is a question about the
SQL/JSON work.

regards, tom lane

TZH and TZM specifiers are required by standard for SQL/JSON item method
.datetime() (Feature F411, “Time zone specification”). To be fully
compliant, we should also support RR, RRRR and FF1-FF9 specifiers.

.datetime() item method is used for conversion of JSON string items to
SQL/JSON datetime items. Its optional argument "format" determines
target datetime type:

=# SELECT jsonb '"10-03-2017 12:34 +05:20"' @*
'$.datetime("DD-MM-YYYY").type()';
?column?
----------
"date"
(1 row)

=# SELECT jsonb '"10-03-2017 12:34 +05:20"' @* '$.datetime("DD-MM-YYYY
HH24:MI").type()';
?column?
-------------------------------
"timestamp without time zone"
(1 row)

=# SELECT jsonb '"10-03-2017 12:34 +05:20"' @* '$.datetime("DD-MM-YYYY
HH24:MI TZH:TZM").type()';
?column?
----------------------------
"timestamp with time zone"
(1 row)

-- automatic datetime type recognition for ISO-formatted strings
=# SELECT jsonb '"2017-10-03 12:34:56 +05:20"' @* '$.datetime().type()';
?column?
----------------------------
"timestamp with time zone"
(1 row)

Here are corresponding excerpts from the SQL-2016 standard:

9.44 Datetime templates

<datetime template> ::=
{ <datetime template part> }...

<datetime template part> ::=
<datetime template field>
| <datetime template delimiter>

<datetime template field> ::=
<datetime template year>
| <datetime template rounded year>
| <datetime template month>
| <datetime template day of month>
| <datetime template day of year>
| <datetime template 12-hour>
| <datetime template 24-hour>
| <datetime template minute>
| <datetime template second of minute>
| <datetime template second of day>
| <datetime template fraction>
| <datetime template am/pm>
| <datetime template time zone hour>
| <datetime template time zone minute>

<datetime template delimiter> ::=
<minus sign>
| <period>
| <solidus>
| <comma>
| <apostrophe>
| <semicolon>
| <colon>
| <space>

<datetime template year> ::= YYYY | YYY | YY | Y
<datetime template rounded year> ::= RRRR | RR
<datetime template month> ::= MM
<datetime template day of month> ::= DD
<datetime template day of year> ::= DDD
<datetime template 12-hour> ::= HH | HH12
<datetime template 24-hour> ::= HH24
<datetime template minute> ::= MI
<datetime template second of minute> ::= SS
<datetime template second of day> ::= SSSSS
<datetime template fraction> ::=
FF1 | FF2 | FF3 | FF4 | FF5 | FF6 | FF7 | FF8 | FF9
<datetime template am/pm> ::= A.M. | P.M.
<datetime template time zone hour> ::= TZH
<datetime template time zone minute> ::= TZM

9.39 SQL/JSON path language: syntax and semantics
...
10) If <JSON datetime template> JDT is specified, then the value of JDT
shall
conform to the lexical grammar of a <datetime template> in the Format of
Subclause 9.44, “Datetime templates”.

a) If JDT contains
<datetime template year>,
<datetime template rounded year>,
<datetime template month>,
<datetime template day of month>, or
<datetime template day of year>,
then JDT is dated.

b) If JDT contains
<datetime template 12-hour>,
<datetime template 24-hour>,
<datetime template minute>,
<datetime template second of minute>,
<datetime template second of day>,
<datetime template fraction>, or
<datetime template am/pm>,
then JDT is timed.

The fractional seconds precision FSP of JDT is
Case:

i) If JDT contains <datetime template fraction>
FF1, FF2, FF3, FF4, FF5, FF6, FF7, FF8, or FF9,
then 1 (one), 2, 3, 4, 5, 6, 7, 8, or 9, respectively.

ii) Otherwise, 0 (zero).

c) If JDT contains
<datetime template time zone hour> or
<datetime template time zone minute>,
then JDT is zoned.

d) If JDT is zoned, then JDT shall be timed.

e) JDT shall be dated or timed or both.
f) The implicit datetime data type IDT of JDT is
Case:
i) If JDT is dated, timed, and zoned, then TIMESTAMP (FSP) WITH TIME
ZONE.
ii) If JDT is dated, timed, and not zoned, then
TIMESTAMP (FSP) WITHOUT TIME ZONE.
iii) If JDT is timed and zoned, then TIME (FSP) WITH TIME ZONE.
iv) If JDT is timed and not zoned, then TIME (FSP) WITHOUT TIME ZONE.
v) If JDT is dated but not timed and not zoned, then DATE.
...

(RR/RRRR specifiers explanation)

9.43 Converting a formatted character string to a datetime
...
5) Let NOW be the value of CURRENT_TIMESTAMP.
Let CY be the YEAR field of NOW.
Let CYLIT be an <unsigned integer> of four <digit>s whose value is
CY.
Let CM be the MONTH field of NOW.
Let CMLIT be an <unsigned integer> of two <digit>s whose value is CM.

6) Case:
a) If CT contains a <datetime template year> YY, then:
i) Let YYPOS be an <exact numeric literal> whose value is the
regular
expression position of YY.
ii) Let YYSTR be the result of SUBSTRING_REGEX ( RX IN FCS GROUP
YYPOS )
iii) Let YYLEN be the length of YYSTR.
iv) Let YYPREFIX be the first (4 – YYLEN) digits of CYLIT.
NOTE 471 — If the length of YYSTR is 4, then YYPREFIX is a
zero-length string.
v) Let YYYY be the result of YYPREFIX || YYSTR
vi) Let YEAR be the value of YYYY interpreted as an <unsigned
integer>.

b) If CT contains a <datetime template rounded year> RR, then:
i) Let RRPOS be an <exact numeric literal> whose value is the
regular
expression position of RR.
ii) Let RRSTR be the result of SUBSTRING_REGEX ( RX IN FCS GROUP
RRPOS )
iii) Let RRLEN be the length of RRSTR.
iv) Let RY be an implementation-defined exact numeric value of
scale 0
(zero) that is between CY–100 and CY+100, inclusive. Let
RYLIT be
an <unsigned integer> of four <digit>s whose value is RY.
v) Let RRPREFIX be the first (4 – RRLEN) digits of RYLIT.
NOTE 472 — If the length of RRSTR is 4, then RRPREFIX is a
zero-length
string.
vi) Let RRRR be the result of RRPREFIX || RRSTR
vii) Let YEAR be the value of RRRR interpreted as an <unsigned
integer>.
c) Otherwise, let YEAR be CY.

I checked this patch and I think so it is correct.

1. all tests passed
2. no problems with patching and compilation
3. the doc is good enough
4. I can confirm so Oracle 12c supports these formats, but I have not
possibility to test it
5. the behave is consistent with timestamp with time zone

I'll mark this patch as ready for commiter

it is not in commitfest as separate entry, so this part of sql/json part is
ready for commiter

Regards

Pavel

Show quoted text

Regards

Pavel

--
Nikita Glukhov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

#13

Andrew Dunstan

andrew.dunstan@2ndquadrant.com

about 8 years ago

In reply to: Pavel Stehule (#11)

Re: to_timestamp TZH and TZM format specifiers

On 01/09/2018 01:46 PM, Pavel Stehule wrote:

I checked this patch and I think so it is correct.

1. all tests passed
2. no problems with patching and compilation
3. the doc is good enough
4. I can confirm so Oracle 12c supports these formats, but I have not
possibility to test it
5. the behave is consistent with timestamp with time zone

I'll mark this patch as ready for commiter

This isn't a separate commitfest item. Since you've replied to an
earlier email it's difficult for me to tell which patch you have
checked? Did you check the one that also implements to_char functionality?

cheers

andrew

--
Andrew Dunstan https://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

#14

Pavel Stehule

pavel.stehule@gmail.com

about 8 years ago

In reply to: Andrew Dunstan (#13)

Re: to_timestamp TZH and TZM format specifiers

2018-01-09 19:52 GMT+01:00 Andrew Dunstan <andrew.dunstan@2ndquadrant.com>:

On 01/09/2018 01:46 PM, Pavel Stehule wrote:

I checked this patch and I think so it is correct.

1. all tests passed
2. no problems with patching and compilation
3. the doc is good enough
4. I can confirm so Oracle 12c supports these formats, but I have not
possibility to test it
5. the behave is consistent with timestamp with time zone

I'll mark this patch as ready for commiter

This isn't a separate commitfest item. Since you've replied to an
earlier email it's difficult for me to tell which patch you have
checked? Did you check the one that also implements to_char functionality?

yes - it works too.

Regards

Pavel

Show quoted text

cheers

andrew

--
Andrew Dunstan https://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

#15

Andrew Dunstan

andrew.dunstan@2ndquadrant.com

about 8 years ago

In reply to: Pavel Stehule (#14)

Re: to_timestamp TZH and TZM format specifiers

On 01/09/2018 01:57 PM, Pavel Stehule wrote:

2018-01-09 19:52 GMT+01:00 Andrew Dunstan
<andrew.dunstan@2ndquadrant.com <mailto:andrew.dunstan@2ndquadrant.com>>:

On 01/09/2018 01:46 PM, Pavel Stehule wrote:

I checked this patch and I think so it is correct.

1. all tests passed
2. no problems with patching and compilation
3. the doc is good enough
4. I can confirm so Oracle 12c supports these formats, but I

have not

possibility to test it
5. the behave is consistent with timestamp with time zone

I'll mark this patch as ready for commiter

This isn't a separate commitfest item. Since you've replied to an
earlier email it's difficult for me to tell which patch you have
checked? Did you check the one that also implements to_char
functionality?

yes - it works too.

Thanks. Committed.

cheers

andrew

--
Andrew Dunstan https://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services