Re: to_timestamp TZH and TZM format specifiers

On 01/03/2018 01:34 PM, Tom Lane wrote:
> Andrew Dunstan <andrew(dot)dunstan(at)2ndquadrant(dot)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