BUG #1458: to_char is crazy

On Wed, Feb 02, 2005 at 12:27:53AM +0000, Tomás Zandoná wrote:

Hey Tom�s,

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.

????? I don't understand why it returns 08 instead of 09...

Is the column of type timestamp or something similar? If so, probably
you are being bitten by a timezone problem. If you want just a date,
declare the column with type date.

--
Alvaro Herrera (<alvherre[@]dcc.uchile.cl>)
Bob [Floyd] used to say that he was planning to get a Ph.D. by the "green
stamp method," namely by saving envelopes addressed to him as 'Dr. Floyd'.
After collecting 500 such letters, he mused, a university somewhere in
Arizona would probably grant him a degree. (Don Knuth)

There is only one row in the table?

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

what happens with:

select * from test_table; or
select day from test_table;

What does your insert statement look like? What is the
column type?

Ted

--- Tom��s Zandon�� <tdzandona@yahoo.com.br> wrote:

The following bug has been logged online:

Bug reference: 1458
Logged by: Tom��s Zandon��
Email address: tdzandona@yahoo.com.br
PostgreSQL version: 7.3.2
Operating system: i don't know
Description: to_char is crazy
Details:

I'm from brazil. I don't wrote English very well.

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.

????? I don't understand why it returns 08 instead
of 09...

Can you help me?
I'm waiting for an answare.
Thanks,
Tom��s Zandon��.

---------------------------(end of
broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

__________________________________
Do you Yahoo!?
Meet the all-new My Yahoo! - Try it today!
http://my.yahoo.com

"Tomás Zandoná" <tdzandona@yahoo.com.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