From: Alex Ignatov <a.ignatov@postgrespro.ru>
To: amul sul <sul_amul@yahoo.co.in>
To: PostgreSQL-development <pgsql-hackers@postgresql.org>
Subject: Re: Bug in to_timestamp().
Date: Mon, 20 Jun 2016 18:22:59 +0300
Message-ID: <f64563b3-5c3c-6f28-3f73-41b2159417bc@postgrespro.ru> (raw)
In-Reply-To: <1873520224.1784572.1465833145330.JavaMail.yahoo@mail.yahoo.com>
References: <1873520224.1784572.1465833145330.JavaMail.yahoo.ref@mail.yahoo.com>
<1873520224.1784572.1465833145330.JavaMail.yahoo@mail.yahoo.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
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
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: a.ignatov@postgrespro.ru, sul_amul@yahoo.co.in
Subject: Re: Bug in to_timestamp().
In-Reply-To: <f64563b3-5c3c-6f28-3f73-41b2159417bc@postgrespro.ru>
* 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