Re: [HACKERS] Bug in to_timestamp().

On Sat, Nov 18, 2017 at 05:48:26PM -0500, Tom Lane wrote:
> This patch needs a rebase over the formatting.c fixes that have gone
> in over the last couple of days.
>
> Looking at the rejects, I notice that in your changes to parse_format(),
> you seem to be making it rely even more heavily on remembering state about
> the previous input. I recommend against that --- it was broken before,
> and it's a pretty fragile approach. Backslashes are not that hard to
> deal with in-line.

I can continue to work on a better approach. Though, the patch was made a long time
ago I'll refresh my memory. The main intent was to fix the following
behaviour:

=# SELECT TO_TIMESTAMP('2016-06-13 15:43:36', 'YYYY/MM/DD HH24:MI:SS');
to_timestamp
------------------------
2016-06-13 05:43:36-07 <— incorrect time

> select to_timestamp('97/Feb/16', 'YY:Mon:DD')
> select to_timestamp('97/Feb/16', 'YY Mon DD')
> select to_timestamp('97 Feb 16', 'YY/Mon/DD')
>
> (Well, Oracle thinks these mean 2097 where we think 1997, but the point is
> you don't get an error.) I see from your regression test additions that
> you want to make some of these throw an error, and I think that any such
> proposal is dead in the water. Nobody is going to consider it an
> improvement if it both breaks working PG apps and disagrees with Oracle.
>
> regards, tom lane

If I understand correctly, these queries don't throw an error and the patch tried to follow Oracle's rules.
Only queries with FX field throw an error. For example:

=# SELECT to_timestamp('97/Feb/16', 'FXYY:Mon:DD');
ERROR: unexpected character "/", expected ":"

From Oracle's documentation [1]:

> FX - Requires exact matching between the character data and the format model.

I agree that compatibility breaking is not good and a fu
ure patch may only try to fix wrong output date and time as in Amul's first email.

1 - https://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements004.htm#i34924

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