Re: Bug in to_timestamp(). - Mailing list pgsql-hackers

<div style="color:#000; background-color:#fff; font-family:Helvetica Neue-Light, Helvetica Neue Light, Helvetica Neue,
Helvetica,Arial, Lucida Grande, Sans-Serif;font-size:16px"><div id="yui_3_16_0_ym19_1_1471272876465_5426">On Thursday,
11August 2016 3:18 PM, Artur Zakirov <a.zakirov@postgrespro.ru> wrote:</div><div
id="yui_3_16_0_ym19_1_1471272876465_5427"><brid="yui_3_16_0_ym19_1_1471272876465_5428" /></div><div
id="yui_3_16_0_ym19_1_1471272876465_5429">>Hereis my patch. It is a proof of concept.</div><div
id="yui_3_16_0_ym19_1_1471272876465_5430">>Date/TimeFormatting</div><div
id="yui_3_16_0_ym19_1_1471272876465_5431">>--------------------</div><div
id="yui_3_16_0_ym19_1_1471272876465_5432">>Thereare changes in date/time formatting rules:</div><div
id="yui_3_16_0_ym19_1_1471272876465_5433">->now to_timestamp() and to_date() skip spaces in the input string
and </div><divid="yui_3_16_0_ym19_1_1471272876465_5434">>in the formatting string unless FX option is used, as Amul
Sulwrote on </div><div id="yui_3_16_0_ym19_1_1471272876465_5435">>first message of this thread. But Ex.2 gives an
errornow with this </div><div id="yui_3_16_0_ym19_1_1471272876465_5436">>patch (should we fix this too?).</div><div
id="yui_3_16_0_ym19_1_1471272876465_5437"><brid="yui_3_16_0_ym19_1_1471272876465_5438" /></div><div
id="yui_3_16_0_ym19_1_1471272876465_5439">Whynot, currently we are skipping whitespace exists at the start of input
stringbut not if in format string.</div><div id="yui_3_16_0_ym19_1_1471272876465_5440"><br
id="yui_3_16_0_ym19_1_1471272876465_5441"/></div><div id="yui_3_16_0_ym19_1_1471272876465_5442">[Skipped… ]</div><div
id="yui_3_16_0_ym19_1_1471272876465_5443"><brid="yui_3_16_0_ym19_1_1471272876465_5444" /></div><div
id="yui_3_16_0_ym19_1_1471272876465_5445">>Ofcourse this patch can be completely wrong. But it tries to
introduce </div><divid="yui_3_16_0_ym19_1_1471272876465_5446">>more formal rules for formatting.</div><div
id="yui_3_16_0_ym19_1_1471272876465_5447">>Iwill be grateful for notes and remarks.</div><div
id="yui_3_16_0_ym19_1_1471272876465_5448"><brid="yui_3_16_0_ym19_1_1471272876465_5449" /></div><div
id="yui_3_16_0_ym19_1_1471272876465_5450">Followingare few scenarios where we break existing behaviour:</div><div
id="yui_3_16_0_ym19_1_1471272876465_5451"><brid="yui_3_16_0_ym19_1_1471272876465_5452" /></div><div
id="yui_3_16_0_ym19_1_1471272876465_5453">SELECTTO_TIMESTAMP('2015-12-31 13:43:36', 'YYYY MM DD HH24 MI SS');</div><div
id="yui_3_16_0_ym19_1_1471272876465_5454">SELECTTO_TIMESTAMP('2011$03!18 23_38_15', 'YYYY-MM-DD HH24:MI:SS');</div><div
id="yui_3_16_0_ym19_1_1471272876465_5455">SELECTTO_TIMESTAMP('2011*03*18 23^38&15', 'YYYY-MM-DD
HH24:MI:SS');</div><divid="yui_3_16_0_ym19_1_1471272876465_5456">SELECT TO_TIMESTAMP('2011*03!18 #%23^38$15',
'YYYY-MM-DD$$$HH24:MI:SS');</div><divid="yui_3_16_0_ym19_1_1471272876465_5457"><br
id="yui_3_16_0_ym19_1_1471272876465_5458"/></div><div id="yui_3_16_0_ym19_1_1471272876465_5459">But current patch
behaviouris not that much bad either at least we have errors, but I am not sure about community acceptance.</div><div
id="yui_3_16_0_ym19_1_1471272876465_5460"><brid="yui_3_16_0_ym19_1_1471272876465_5461" /></div><div
id="yui_3_16_0_ym19_1_1471272876465_5462">Iwould like to divert communities' attention on following case:</div><div
id="yui_3_16_0_ym19_1_1471272876465_5463">SELECTTO_TIMESTAMP('2013--10-01', 'YYYY-MM-DD');</div><div
id="yui_3_16_0_ym19_1_1471272876465_5464"><brid="yui_3_16_0_ym19_1_1471272876465_5465" /></div><div
id="yui_3_16_0_ym19_1_1471272876465_5466">Wherethe hyphen (-) is not skipped. So ultimately -10 is interpreted using MM
asnegative 10. So the date goes back by that many months (and probably additional days because of -31), and so the
finaloutput becomes 2012-01-30. But the fix is not specific to hyphen case. Ideally the fix would have been to handle
itin from_char_parse_int(). Here, -10 is converted to int using strtol. May be we could have done it using strtoul().
Isthere any intention behind not considering signed integers versus unsigned ones ?</div><div
id="yui_3_16_0_ym19_1_1471272876465_5467"><brid="yui_3_16_0_ym19_1_1471272876465_5468" /></div><div
id="yui_3_16_0_ym19_1_1471272876465_5469">Anotheris, shouldn’t we have error in following cases? </div><div
id="yui_3_16_0_ym19_1_1471272876465_5470">SELECTTO_TIMESTAMP('2016-06-13 99:99:99', 'YYYY-MM-DD
HH24:MI:SS'); </div><divid="yui_3_16_0_ym19_1_1471272876465_5471">SELECT TO_TIMESTAMP('2016-02-30 15:43:36',
'YYYY-MM-DDHH24:MI:SS');</div><div id="yui_3_16_0_ym19_1_1471272876465_5472"><br
id="yui_3_16_0_ym19_1_1471272876465_5473"/></div><div id="yui_3_16_0_ym19_1_1471272876465_5474"><br
id="yui_3_16_0_ym19_1_1471272876465_5475"/></div><div id="yui_3_16_0_ym19_1_1471272876465_5476">Thanks  &
Regards,</div><divid="yui_3_16_0_ym19_1_1471272876465_5477">Amul Sul</div><div dir="ltr"
id="yui_3_16_0_ym19_1_1471272876465_5478"><brid="yui_3_16_0_ym19_1_1471272876465_5479" /></div></div>