Thread: to_timestamp TZH and TZM format specifiers

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

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

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

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

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

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

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

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

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

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.

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

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

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