Re: to_timestamp TZH and TZM format specifiers

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.

> 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