Re: to_timestamp TZH and TZM format specifiers
От | Andrew Dunstan |
---|---|
Тема | Re: to_timestamp TZH and TZM format specifiers |
Дата | |
Msg-id | 99ee57d4-e4c5-aa73-38ce-36dcca6fe553@2ndQuadrant.com обсуждение исходный текст |
Ответ на | Re: to_timestamp TZH and TZM format specifiers (Tom Lane <tgl@sss.pgh.pa.us>) |
Ответы |
Re: to_timestamp TZH and TZM format specifiers
(Tom Lane <tgl@sss.pgh.pa.us>)
Re: to_timestamp TZH and TZM format specifiers (Andrew Dunstan <andrew.dunstan@2ndquadrant.com>) |
Список | pgsql-hackers |
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
В списке pgsql-hackers по дате отправления:
Предыдущее
От: Robert HaasДата:
Сообщение: Re: Better testing coverage and unified coding for plpgsql loops