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