Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1bZJL2-0007YO-Id for pgsql-hackers@arkaria.postgresql.org; Mon, 15 Aug 2016 14:58:05 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1bZJL2-0007Xe-5h for pgsql-hackers@arkaria.postgresql.org; Mon, 15 Aug 2016 14:58:04 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1bZJKz-0007T1-IN for pgsql-hackers@postgresql.org; Mon, 15 Aug 2016 14:58:02 +0000 Received: from nm18-vm10.bullet.mail.sg3.yahoo.com ([106.10.149.105]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1bZJKv-0004lc-0S for pgsql-hackers@postgresql.org; Mon, 15 Aug 2016 14:58:01 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.co.in; s=s2048; t=1471273069; bh=aXu/MtSxMfBxu5JWTxELua6JnhxmJRhyIzi+9S/mltk=; h=Date:From:Reply-To:To:Cc:In-Reply-To:References:Subject:From:Subject; b=iK0tngn10vM//smlBIwe8nf/XEkBU8wosnaR7LtMFUTHsWHr4rchwBBEJVkrRmvARf4YLw+01dcbIijVLMu8hpIUmicIsuC62PNxmpzhnYldwYFAdZlbDWWsT0/bBA3cQr7KH/T9lC5mGXu60DBeYqzY2idK6ceZhmjUMU/4UhHr58vFCHZn9C7YsdjGCGQXBtJkRc/UuJFank4saPwH+4uoFYz7ZFFn+kMJfn1LZigDJKQg0a55/UsHkJXSlESE+PLevfJpiyAfx2kEpqTtsAFqgL85XC8GwYMg6z7Lpv5bVeD621qMPU0abWEML0Pv75+MeZGTge16l//O1Rmmng== Received: from [106.10.166.121] by nm18.bullet.mail.sg3.yahoo.com with NNFMP; 15 Aug 2016 14:57:49 -0000 Received: from [106.10.151.252] by tm10.bullet.mail.sg3.yahoo.com with NNFMP; 15 Aug 2016 14:57:49 -0000 Received: from [127.0.0.1] by omp1001.mail.sg3.yahoo.com with NNFMP; 15 Aug 2016 14:57:49 -0000 X-Yahoo-Newman-Property: ymail-3 X-Yahoo-Newman-Id: 773212.17476.bm@omp1001.mail.sg3.yahoo.com X-YMail-OSG: _qHhQvAVM1luW4Y5XvyizKF0bf__R1c9olkc2vgLd1umK.3SNU1RqhAZ9.X7l6e Razskji55Na_vi8MkJdE5eLwU2u7NcqjdSe1s.VBKbX5OOfYGloRd.V7AfRpsTCghbXpsxunT00v x1_d15jj1uQhVyJ2xFPcTYCoelmRb80opsuMKayWmE1jj3.uhARPYf5VGI1eKvSkfh2RZNc7j28e JR2BknvmUK_hIWrMXM7CdxgHE9ahqjYaPyZLUjXr6FSFzkPwbaAVIoLiurIdJe5Gd1DHcG4xCjQE aEY2esJgNJWr71SZyWul.JS8nRSoTRbwdcGZYpyjFvcH_3w78Gfg_8Xpl6u7XE98TJfRQWQrOIyb WBacmqyk80JY2V31pWv5LSJeN0muOpMkFOKAaqDWKzsx3PHE3wLsdS4EQK90AdoLdvVkgfDY00hJ wDtProMnWxzp8c1r0WxE7401bee5n9pW5ZQfgq1fsiIEM5znrtZtq6HS_KFEEJuena0KQIJiz_mP rfHNA.LAHU3LWYFc_AaXNXDr9ExocUK.76zmqLH0PuW6kinnQFL05 Received: from jws10987.mail.sg3.yahoo.com by sendmailws116.mail.sg3.yahoo.com; Mon, 15 Aug 2016 14:57:49 +0000; 1471273069.395 Date: Mon, 15 Aug 2016 14:56:34 +0000 (UTC) From: amul sul Reply-To: amul sul To: Artur Zakirov , Pavel Stehule Cc: Tom Lane , Robert Haas , "David G. Johnston" , Alex Ignatov , Bruce Momjian , PostgreSQL-development Message-ID: <1285870380.13225431.1471272994354.JavaMail.yahoo@mail.yahoo.com> In-Reply-To: References: <1873520224.1784572.1465833145330.JavaMail.yahoo.ref@mail.yahoo.com> <1873520224.1784572.1465833145330.JavaMail.yahoo@mail.yahoo.com> <884791796.213185.1466667686999.JavaMail.yahoo@mail.yahoo.com> <20160623133001.GF21246@momjian.us> <1877e60a-a970-18b5-453c-e342f96150e6@postgrespro.ru> <31915.1466703655@sss.pgh.pa.us> <32684.1466704957@sss.pgh.pa.us> <734e50cd-c563-4afd-d1c8-80f244b3265e@postgrespro.ru> Subject: Re: Bug in to_timestamp(). MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_13225430_111181903.1471272994348" Content-Length: 7872 X-Pg-Spam-Score: -2.7 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-hackers Precedence: bulk Sender: pgsql-hackers-owner@postgresql.org ------=_Part_13225430_111181903.1471272994348 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable On Thursday, 11 August 2016 3:18 PM, Artur Zakirov wrote: >Here is my patch. It is a proof of concept.>Date/Time Formatting>---------= ----------->There are changes in date/time formatting rules:-> now to_times= tamp() and to_date() skip spaces in the input string and=C2=A0>in the forma= tting string unless FX option is used, as Amul Sul wrote on=C2=A0>first mes= sage of this thread. But Ex.2 gives an error now with this=C2=A0>patch (sho= uld 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=E2=80=A6 ] >Of course this patch can be completely wrong. But it tries to introduce=C2= =A0>more formal rules for formatting.>I will be grateful for notes and rema= rks. 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_TIME= STAMP('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 er= rors, but I am not sure about community acceptance. I would like to divert communities' attention on following case:SELECT TO_T= IMESTAMP('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-3= 0. But the fix is not specific to hyphen case. Ideally the fix would have b= een to handle it in from_char_parse_int(). Here, -10 is converted to int us= ing strtol. May be we could have done it using strtoul(). Is there any inte= ntion behind not considering signed integers versus unsigned ones ? Another is, shouldn=E2=80=99t we have error in following cases?=C2=A0SELECT= TO_TIMESTAMP('2016-06-13 99:99:99', 'YYYY-MM-DD HH24:MI:SS');=C2=A0SELECT = TO_TIMESTAMP('2016-02-30 15:43:36', 'YYYY-MM-DD HH24:MI:SS'); Thanks =C2=A0& Regards,Amul Sul ------=_Part_13225430_111181903.1471272994348 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
On Thursday, 11 August 2016 3:18 PM, Artur Za= kirov <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 t= o_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 n= ow with this 
&g= t;patch (should we fix this too?).

Why not, currently we are skippin= g whitespace exists at the start of input string but not if in format strin= g.

[Skipped=E2=80=A6 ]

>Of course this patch can be comple= tely 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 b= ehaviour:

SELECT TO_TIMESTAMP('2015-12-31 13:43:36', 'YYYY MM DD HH2= 4 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$$$H= H24:MI:SS');

But current patch behaviour is not that much bad eith= er 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 ult= imately -10 is interpreted using MM as negative 10. So the date goes back b= y that many months (and probably additional days because of -31), and so th= e final output becomes 2012-01-30. But the fix is not specific to hyphen ca= se. 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 us= ing strtoul(). Is there any intention behind not considering signed integer= s versus unsigned ones ?

Another is, shouldn=E2=80=99t we have error= in following cases? 
SELECT TO_TIMESTAMP('2016-06-13 99:99:99', 'YYYY-MM-DD HH24:MI:SS');&= nbsp;
SELECT TO_TIMES= TAMP('2016-02-30 15:43:36', 'YYYY-MM-DD HH24:MI:SS');


Thanks  & Regards,
Amul Sul

=
------=_Part_13225430_111181903.1471272994348--