Bug in function to_char() !!

[Najib Abi Fadel]

Hi, i am using PostgreSQL 7.3.2

there's a bug for the date '2005-03-27' !!!!!!!!!!!!!!! !!!!!!!!!!!!

SELECT to_char('2005-03-27'::date,'DD/MM/YYYY');
to_char
------------
26/03/2005
(1 row)

I get the date 26/03/2005 instead of 27/03/2005 !!!

For other dates the function works well !!

[Najib Abi Fadel]

[root@STIsrv root]# uname -a
Linux STIsrv 2.4.18-14 #1 Wed Sep 4 13:35:50 EDT 2002 i686 i686 i386
GNU/Linux

[root@STIsrv root]# date
Mon Jul 5 13:22:55 EEST 2004

I tried to change the timezone (using timeconfig) i have always:

dragon_devel=# SELECT to_char('2005-03-27'::date,'DD/MM/YY');
to_char
----------
26/03/05
(1 row)

NOTE THAT IF I USE TIMESTAMP IT WORKS::
dragon_devel=# SELECT to_char('2005-03-27'::timestamp, 'DD/MM/YY');
to_char
----------
27/03/05
(1 row)

i am using Red HAt 8.0

I have tried the same command on 2 different servers (same postgres version)

dragon_devel=# SELECT version();
version
----------------------------------------------------------------------------
-----------------------------
PostgreSQL 7.3.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2
20020903 (Red Hat Linux 8.0 3.2-7)
(1 row)
----- Original Message -----
From: "Tom Lane" <tg*@sss.pgh.pa .us>
To: "Najib Abi Fadel" <na*******@usj. edu.lb>
Cc: "generalpos t" <pg***********@ postgresql.org>
Sent: Thursday, July 01, 2004 4:52 PM
Subject: Re: [GENERAL] Bug in function to_char() !!

"Najib Abi Fadel" <na*******@usj. edu.lb> writes:

SELECT to_char('2005-03-27'::date,'DD/MM/YYYY');
to_char
------------
26/03/2005
(1 row)

What timezone setting are you using, and what kind of system is this on?
I suppose that day is a daylight-savings transition day for you, but no
one else is likely to reproduce the problem in a different zone ...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

[Tom Lane]

"Najib Abi Fadel" <na*******@usj. edu.lb> writes:

I tried to change the timezone (using timeconfig)

You didn't answer the question though: what timezone are you using?

If "SHOW timezone" produces something specific, that is the answer.
If it says "unknown" then what you will need to do is work out
which of the files under /usr/share/zoneinfo is an exact match
for /etc/localtime.

Also, was your Postgres built with --enable-integer-datetimes by
any chance? (Look at the output of pg_config --configure if you
are not sure.)

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org

[Najib Abi Fadel]

> You didn't answer the question though: what timezone are you using?
Asia/Beirut

If "SHOW timezone" produces something specific, that is the answer.
If it says "unknown" then what you will need to do is work out Show timezone says "unknown" which of the files under /usr/share/zoneinfo is an exact match
for /etc/localtime. I have a file : /usr/share/zoneinfo/Asia/Beirut
Also, was your Postgres built with --enable-integer-datetimes by
any chance? (Look at the output of pg_config --configure if you
are not sure.)

pg_config --configure
'CC=/usr/bin/gcc'

PS : I want just to remember that the to_char(date,'D D/MM/YYYY') works fine
for all dates except the date '2005-03-27'

SELECT to_char('2005-03-27'::date,'DD/MM/YYYY');
to_char
------------
26/03/2005

SELECT to_char('2005-03-20'::date,'DD/MM/YYYY');
to_char
------------
20/03/2005
SELECT to_char('2004-06-07'::date,'DD/MM/YYYY');
to_char
------------
07/06/2004

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Tom Lane]

"Najib Abi Fadel" <na*******@usj. edu.lb> writes:

You didn't answer the question though: what timezone are you using?

Asia/Beirut

Okay, with that I can reproduce it. That zone is one of the ones where
the DST transitions occur just at midnight. So there really isn't any
"midnight local time" on that date; the first valid local time is 1AM.

The reason to_char() is showing this behavior is there is no
to_char(date) function, only to_char(timesta mp). If you look at what
the implied promotion is doing, you see

regression=> set TimeZone TO 'Asia/Beirut';
SET
regression=> SELECT '2005-03-27'::date::time stamptz;
timestamptz
------------------------
2005-03-26 23:00:00+02
(1 row)

Presented with the invalid local time '2005-03-27 00:00:00', the
timestamp converter chooses to treat it as midnight in the local
daylight-savings time, which is more conventionally written as 11PM
standard time. And then of course your to_char() format only shows
the date part of that.

7.4 and later are more consistent about what is done with "invalid"
local times: they always treat an invalid or ambiguous time as being
local standard time. So in 7.4 and later your example works as
desired:

regression=# set TimeZone TO 'Asia/Beirut';
SET
regression=# SELECT '2005-03-27'::date::time stamptz;
timestamptz
------------------------
2005-03-27 01:00:00+03
(1 row)

"Midnight standard time" is more conventionally 1AM daylight time,
and then you get the right result when looking only at the date part.

Of course this just shifts the locus of pain: if there were any
timezones that switched at 11PM, they'd have funny behavior instead.
But AFAIK there aren't any.

Bottom line: update to 7.4. You could hack around it in 7.3 by
explicitly promoting the date to timestamp without time zone
(or better, make a function to_char(date) to do it for you)
but I think your time would be better spent on an update.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

[Najib Abi Fadel]

Bottom line: update to 7.4. You could hack around it in 7.3 by
explicitly promoting the date to timestamp without time zone
(or better, make a function to_char(date) to do it for you)
but I think your time would be better spent on an update.

Since i can't update to 7.4 (for internal reasons),
I created the following function to_char(date,te xt) :
CREATE function to_char(date,te xt) returns text as ' select
to_char($1::tim estamp,$2);' language sql;

It worked well

thx
Najib.

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)