Subject: Re: Bug in to_timestamp().
To: Tom Lane <tgl@sss.pgh.pa.us>, Robert Haas <robertmhaas@gmail.com>
References: 
 <1873520224.1784572.1465833145330.JavaMail.yahoo.ref@mail.yahoo.com>
 <1873520224.1784572.1465833145330.JavaMail.yahoo@mail.yahoo.com>
 <20932.1465834344@sss.pgh.pa.us>
 <CA+TgmoaD8TuS=sHKS1f1qAfizCBouejgmO2jYnJ_=ia343uGSA@mail.gmail.com>
 <CA+TgmoZKJOoywaBT-K_pmQT4yPuDmR5AxjcEdwiCofA5Q3S0yA@mail.gmail.com>
 <11349.1466429771@sss.pgh.pa.us>
Cc: amul sul <sul_amul@yahoo.co.in>,
 PostgreSQL-development <pgsql-hackers@postgresql.org>
From: Alex Ignatov <a.ignatov@postgrespro.ru>
Message-ID: <0f8caaa5-9d9d-ff3f-a7a3-223aefc96da4@postgrespro.ru>
Date: Mon, 20 Jun 2016 18:15:50 +0300
User-Agent: Mozilla/5.0 (Windows NT 10.0; WOW64; rv:45.0) Gecko/20100101
 Thunderbird/45.1.1
MIME-Version: 1.0
In-Reply-To: <11349.1466429771@sss.pgh.pa.us>
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org


On 20.06.2016 16:36, Tom Lane wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>> On Mon, Jun 13, 2016 at 12:25 PM, Robert Haas <robertmhaas@gmail.com> wrote:
>>> I think a space in the format string should skip a whitespace
>>> character in the input string, but not a non-whitespace character.
>>> It's my understanding that these functions exist in no small part for
>>> compatibility with Oracle, and Oracle declines to skip the digit '1'
>>> on the basis of an extra space in the format string, which IMHO is the
>>> behavior any reasonable user would expect.
>> So Amul and I are of one opinion and Tom is of another.  Anyone else
>> have an opinion?
> I don't necessarily have an opinion yet.  I would like to see more than
> just an unsupported assertion about what Oracle's behavior is.  Also,
> how should FM mode affect this?
>
> 			regards, tom lane
>
>

Oracle:
SQL> SELECT TO_TIMESTAMP('2016-06-13 99:99:99', 'YYYYMMDD HH24:MI:SS') 
from dual;
SELECT TO_TIMESTAMP('2016-06-13 99:99:99', 'YYYYMMDD HH24:MI:SS') from dual
                     *
ERROR at line 1:
ORA-01843: not a valid month

PG:

postgres=# SELECT TO_TIMESTAMP('2016-06-13 99:99:99', 'YYYYMMDD 
HH24:MI:SS');
       to_timestamp
------------------------
  2016-01-06 14:40:39+03
(1 row)


I know about:
"These functions interpret input liberally, with minimal error checking. 
While they produce valid output, the conversion can yield unexpected 
results" from docs but by providing illegal input parameters  we have no 
any exceptions or errors about that.
I think that to_timestamp() need to has more format checking than it has 
now.

Alex Ignatov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers