Re: BUG #1458: to_char is crazy

"Toms Zandon" <tdzandona(at)yahoo(dot)com(dot)br> writes:
> I dont't know if it's a bug, but I insert the date 2005-10-09 in a Date
> field.
> Later I execute:

> select to_char(day, 'DD/MM/YYYY') from test_table;

> It returns 08/10/2005.

What timezone are you using, and is midnight 2005-10-09 a
daylight-savings transition time there? If so, this is a known bug
in PG 7.3 --- update to 7.4 or later to fix it. As a workaround you
could explicitly cast the date value to timestamp without time zone
before feeding it to to_char.

My Fedora machine thinks that the October 2005 transition day in Brazil
is the 16th, but you might be using a different timezone database. 7.3
gets the promotion from date to timestamp-with-zone wrong:

regression=# set TimeZone TO 'America/Sao_Paulo';
SET
regression=# select ('2005-10-16'::date)::timestamp with time zone;
timestamptz
------------------------
2005-10-15 23:00:00-03
(1 row)

7.4 and later produce '2005-10-16 01:00:00-02' which is more reasonable
(the point being that local midnight doesn't actually exist due to the
DST spring forward).

regards, tom lane