Bug in to_timestamp().

amulsul <sul_amul@yahoo.co.in>

From: amul sul <sul_amul@yahoo.co.in>
To: PostgreSQL-development <pgsql-hackers@postgresql.org>
Date: 2016-06-13T15:52:25Z
Lists: pgsql-hackers
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


Commits

  1. Improve behavior of to_timestamp()/to_date() functions

  2. Implement TZH and TZM timestamp format patterns

  3. as attache of this mail is patch (to the main tree) with to_char's