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