On 13.06.2016 18:52, amul sul wrote:

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');
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');
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.
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');
 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:

Reply via email to