From: | amul sul <sul_amul(at)yahoo(dot)co(dot)in> |
---|---|
To: | Artur Zakirov <a(dot)zakirov(at)postgrespro(dot)ru>, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Robert Haas <robertmhaas(at)gmail(dot)com>, "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>, Alex Ignatov <a(dot)ignatov(at)postgrespro(dot)ru>, Bruce Momjian <bruce(at)momjian(dot)us>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Bug in to_timestamp(). |
Date: | 2016-08-15 14:56:34 |
Message-ID: | 1285870380.13225431.1471272994354.JavaMail.yahoo@mail.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Thursday, 11 August 2016 3:18 PM, Artur Zakirov <a(dot)zakirov(at)postgrespro(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Vladimir Sitnikov | 2016-08-15 15:16:17 | Re: Slowness of extended protocol |
Previous Message | Anastasia Lubennikova | 2016-08-15 14:45:58 | Re: WIP: Covering + unique indexes. |