From: amul sul <sul_amul@yahoo.co.in>
To: Artur Zakirov <a.zakirov@postgrespro.ru>
To: Pavel Stehule <pavel.stehule@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: David G. Johnston <david.g.johnston@gmail.com>
Cc: Alex Ignatov <a.ignatov@postgrespro.ru>
Cc: Bruce Momjian <bruce@momjian.us>
Cc: PostgreSQL-development <pgsql-hackers@postgresql.org>
Subject: Re: Bug in to_timestamp().
Date: Mon, 15 Aug 2016 14:56:34 +0000 (UTC)
Message-ID: <1285870380.13225431.1471272994354.JavaMail.yahoo@mail.yahoo.com> (raw)
In-Reply-To: <b2a39359-3282-b402-f4a3-057aae500ee7@postgrespro.ru>
References: <1873520224.1784572.1465833145330.JavaMail.yahoo.ref@mail.yahoo.com>
<1873520224.1784572.1465833145330.JavaMail.yahoo@mail.yahoo.com>
<f64563b3-5c3c-6f28-3f73-41b2159417bc@postgrespro.ru>
<884791796.213185.1466667686999.JavaMail.yahoo@mail.yahoo.com>
<20160623133001.GF21246@momjian.us>
<1877e60a-a970-18b5-453c-e342f96150e6@postgrespro.ru>
<CAKFQuwZKtS0Ad=a1E9_mkdYwLMq+JA3YjmhCvjJ1JF+3BU8a0g@mail.gmail.com>
<d469b177-fd4d-5a5b-769e-620957ec1a64@postgrespro.ru>
<CAKFQuwb2gp8n2ai_agyJod5BTYxL7ek0AjB9zifDSceK6QntnQ@mail.gmail.com>
<CA+TgmoaWTgbtd2RTwVvySMVoOcHmWq66rCCxtVywOOX3FJy4tQ@mail.gmail.com>
<31915.1466703655@sss.pgh.pa.us>
<CA+TgmoaFQZeqmAo3CdivNu70coB=9eM5OroKYtNcTuMaC1kuUQ@mail.gmail.com>
<32684.1466704957@sss.pgh.pa.us>
<734e50cd-c563-4afd-d1c8-80f244b3265e@postgrespro.ru>
<CAFj8pRDGxe=cdcZ7pur=2WeW9O3MQCZTBEAOJfQ7OLQJaYXDvw@mail.gmail.com>
<b2a39359-3282-b402-f4a3-057aae500ee7@postgrespro.ru>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: sul_amul@yahoo.co.in, a.zakirov@postgrespro.ru, pavel.stehule@gmail.com, tgl@sss.pgh.pa.us, robertmhaas@gmail.com, david.g.johnston@gmail.com, a.ignatov@postgrespro.ru, bruce@momjian.us
Subject: Re: Bug in to_timestamp().
In-Reply-To: <1285870380.13225431.1471272994354.JavaMail.yahoo@mail.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox