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