Subject: Re: Bug in to_timestamp().
To: amul sul <sul_amul@yahoo.co.in>,
 PostgreSQL-development <pgsql-hackers@postgresql.org>
References: 
 <1873520224.1784572.1465833145330.JavaMail.yahoo.ref@mail.yahoo.com>
 <1873520224.1784572.1465833145330.JavaMail.yahoo@mail.yahoo.com>
From: Alex Ignatov <a.ignatov@postgrespro.ru>
Message-ID: <f64563b3-5c3c-6f28-3f73-41b2159417bc@postgrespro.ru>
Date: Mon, 20 Jun 2016 18:22:59 +0300
User-Agent: Mozilla/5.0 (Windows NT 10.0; WOW64; rv:45.0) Gecko/20100101
 Thunderbird/45.1.1
MIME-Version: 1.0
In-Reply-To: <1873520224.1784572.1465833145330.JavaMail.yahoo@mail.yahoo.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org


On 13.06.2016 18:52, amul sul wrote:
> Hi,
>
> It's look like bug in to_timestamp() function when format string has more whitespaces compare to input string, see below:
>
> Ex.1: Two white spaces before HH24 whereas one before input time string
>
> postgres=# SELECT TO_TIMESTAMP('2016-06-13 15:43:36', 'YYYY/MM/DD  HH24:MI:SS');
> to_timestamp
> ------------------------
> 2016-06-13 05:43:36-07       <— incorrect time
> (1 row)
>
>
>
> Ex.2: One whitespace before YYYY format string
>
> postgres=# SELECT TO_TIMESTAMP('2016/06/13 15:43:36', ' YYYY/MM/DD HH24:MI:SS');
> to_timestamp
> ------------------------------
> 0016-06-13 15:43:36-07:52:58      <— incorrect year
> (1 row)
>
>
>
> If there are one or more consecutive whitespace in the format, we should skip those as long as we could get an actual field.
> Thoughts?
> Thanks & Regards,
> Amul Sul
>
>

 From docs about to_timestamp() ( 
https://www.postgresql.org/docs/9.5/static/functions-formatting.html)
"These functions interpret input liberally, with minimal error checking. 
While they produce valid output, the conversion can yield unexpected 
results. For example, input to these functions is not restricted by 
normal ranges, thus to_date('20096040','YYYYMMDD') returns 2014-01-17 
rather than causing an error. Casting does not have this behavior."

And it wont stop on some simple whitespace. By using to_timestamp you 
can get any output results by providing illegal input parameters values:

postgres=# SELECT TO_TIMESTAMP('2016-06-13 99:99:99', 'YYYYMMDD 
HH24:MI:SS');
       to_timestamp
------------------------
  2016-01-06 14:40:39+03
(1 row)


Alex Ignatov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers