Re: Bug in to_timestamp().

On 15.08.2016 19:28, Robert Haas wrote:
>
> Well, what's the Oracle behavior in any of these cases?  I don't think
> we can decide to change any of this behavior without knowing that.  If
> a proposed behavior change is incompatible with our previous releases,
> I think it'd better at least be more compatible with Oracle.
> Otherwise, we're just changing from an established behavior that we
> invented ourselves to a new behavior we invented ourselves, which is
> only worthwhile if it's absolutely clear that the new behavior is way
> better.
>

1 - Oracle's output for first queries is:

-> SELECT TO_TIMESTAMP('2015-12-31 13:43:36', 'YYYY MM DD HH24 MI SS') 
FROM dual;

TO_TIMESTAMP('2015-12-3113:43:36','YYYYMMDDHH24MISS')
---------------------------------------------------------------------------
31-DEC-15 01.43.36.000000000 PM

-> SELECT TO_TIMESTAMP('2011$03!18 23_38_15', 'YYYY-MM-DD HH24:MI:SS') 
FROM dual;

TO_TIMESTAMP('2011$03!1823_38_15','YYYY-MM-DDHH24:MI:SS')
---------------------------------------------------------------------------
18-MAR-11 11.38.15.000000000 PM

-> SELECT TO_TIMESTAMP('2011*03!18 #%23^38$15', 
'YYYY-MM-DD$$$HH24:MI:SS') FROM dual;

TO_TIMESTAMP('2011*03!18#%23^38$15','YYYY-MM-DD$$$HH24:MI:SS')
---------------------------------------------------------------------------
18-MAR-11 11.38.15.000000000 PM

PostgreSQL with the patch gives "ERROR:  expected space character in 
given string". I will fix this.


2 - Oracle's output for query with hyphen is:

-> SELECT TO_TIMESTAMP('2013--10-01', 'YYYY-MM-DD') FROM dual;
SELECT TO_TIMESTAMP('2013--10-01', 'YYYY-MM-DD') FROM dual
                     *
ERROR at line 1:
ORA-01843: not a valid month

Here PostgreSQL with the patch does not give an error. So I will fix 
this too.


3 - The last two queries give an error. This patch do not handle such 
queries intentionally, because there is the thread 
https://www.postgresql.org/message-id/57786490.9010201%40wars-nicht.de . 
That thread concerns to_date() function. But it should concerns 
to_timestamp() also. So I suppose that should be a different patch for 
this last case.

-- 
Artur Zakirov
Postgres Professional: http://www.postgrespro.com
Russian Postgres Company