Обсуждение: BUG #4539: to_char(to_timestamp('...','....')) returns the wrong result

The following bug has been logged online:

Bug reference:      4539
Logged by:          Stuart Green
Email address:      stuart@greenfam.com
PostgreSQL version: 8.2.11
Operating system:   Centos 5
Description:        to_char(to_timestamp('...','....')) returns the wrong
result
Details:

The query

select to_char(to_timestamp('2008-11-14 16:00:00','YYYY-MM-DD
HH24:MM:SS'),'day');

is returning monday as the day in all the dbase version I Have test, 8.2.11,
8.2.10, and 8.1.11

The query

select to_char(to_timestamp('2008-11-14','YYYY-MM-DD'),'day');

returns friday, which is correct, if you replace to_timestamp with to_date
it returns friday which is correct.

Specify the 24 hour clock or not in the timestamp query doesn't make a
difference in the result, it still returns monday, when it should be friday.

Stuart Green wrote:
> select to_char(to_timestamp('2008-11-14 16:00:00','YYYY-MM-DD
> HH24:MM:SS'),'day');
>
> is returning monday as the day in all the dbase version I Have test, 8.2.11,
> 8.2.10, and 8.1.11

Use 'MI' instead of 'MM' for the minutes. to_timestamp is quite lenient,
and interpretes the 00 in what you meant to be the minutes field as
month number. As there is no month 0, it uses January.

to_timestamp is much stricter in the upcoming 8.4 release, and will
throw an error for that query:

postgres=# select to_char(to_timestamp('2008-11-14 16:00:00','YYYY-MM-DD
HH24:MM:SS'),'day');
ERROR:  conflicting values for "MM" field in formatting string
DETAIL:  This value contradicts a previous setting for the same field type.

--
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com