Re: Bug in to_timestamp().

On Thursday, 11 August 2016 3:18 PM, Artur Zakirov <a.zakirov@postgrespro.ru> wrote:

>Here is my patch. It is a proof of concept.

>Date/Time Formatting

>--------------------

>There are changes in date/time formatting rules:

-> now to_timestamp() and to_date() skip spaces in the input string and 

>in the formatting string unless FX option is used, as Amul Sul wrote on 

>first message of this thread. But Ex.2 gives an error now with this 

>patch (should we fix this too?).

Why not, currently we are skipping whitespace exists at the start of input string but not if in format string.

[Skipped… ]

>Of course this patch can be completely wrong. But it tries to introduce 

>more formal rules for formatting.

>I will be grateful for notes and remarks.

Following are few scenarios where we break existing behaviour:

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

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

SELECT TO_TIMESTAMP('2011*03*18 23^38&15', 'YYYY-MM-DD HH24:MI:SS');

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

But current patch behaviour is not that much bad either at least we have errors, but I am not sure about community acceptance.

I would like to divert communities' attention on following case:

SELECT TO_TIMESTAMP('2013--10-01', 'YYYY-MM-DD');

Where the hyphen (-) is not skipped. So ultimately -10 is interpreted using MM as negative 10. So the date goes back by that many months (and probably additional days because of -31), and so the final output becomes 2012-01-30. But the fix is not specific to hyphen case. Ideally the fix would have been to handle it in from_char_parse_int(). Here, -10 is converted to int using strtol. May be we could have done it using strtoul(). Is there any intention behind not considering signed integers versus unsigned ones ?

Another is, shouldn’t we have error in following cases? 

SELECT TO_TIMESTAMP('2016-06-13 99:99:99', 'YYYY-MM-DD HH24:MI:SS'); 

SELECT TO_TIMESTAMP('2016-02-30 15:43:36', 'YYYY-MM-DD HH24:MI:SS');

Thanks  & Regards,

Amul Sul