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 !!

[Dirk Försterling]

Najib Abi Fadel wrote:

Hi, i am using PostgreSQL 7.3.2

there's a bug for the date '2005-03-27' !!!!!!!!!!!!!!!!!!!!!!!!!!!
I think you should replace or clean your keyboard. At least one key
seems to be sticky.
SELECT to_char('2005-03-27'::date,'DD/MM/YYYY');
to_char
------------
26/03/2005
(1 row)

No problem with 7.2.1 and 7.4.1. (don't have a 7.3.2 around).
Both work correctly:

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

--
D i r k F "o r s t e r l i n g
[e-mail] http://r.zorbla.de/
-------------
"A radioactive cat has eighteen half-lives."

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

[Karel Zak]

On Thu, Jul 01, 2004 at 09:47:38AM +0200, Najib Abi Fadel wrote:

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 !!!

7.5devel:

# SELECT to_char('2005-03-27'::date,'DD/MM/YYYY');
to_char
------------
27/03/2005
The date looks like some date when is daylight saving time change,
maybe it's something already knows and fixed.

Can you check results of:

# SELECT '2005-03-27'::timestamp;
timestamp
---------------------
2005-03-27 00:00:00

# SHOW TIMEZONE;
TimeZone
---------------
Europe/Prague
Karel

--
Karel Zak <za***@zf.jcu.cz>
http://home.zf.jcu.cz/~zakkr/

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

http://www.postgresql.org/docs/faqs/FAQ.html

[Stephane Bortzmeyer]

On Thu, Jul 01, 2004 at 09:53:34AM +0200,
Dirk Försterling <[e-mail]> wrote
a message of 33 lines which said:

No problem with 7.2.1 and 7.4.1. (don't have a 7.3.2 around).

7.3.4 seems OK so I assume the guy with the exclamation marks did not
type what he sent:

% psql template1
Welcome to psql 7.3.4, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit

template1=> SELECT to_char('2005-03-27'::date,'DD/MM/YYYY');
to_char
------------
27/03/2005
(1 row)
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Clodoaldo Pinto Neto]

[cpn@s00 work]$ rpm -q postgresql
postgresql-7.3.4-11

Fedora Core 1

KakaoStats=# SELECT
to_char('2005-03-27'::date,'DD/MM/YYYY');
to_char
------------
27/03/2005
(1 row)
--- Dirk_Försterling <[e-mail]> escreveu: > Najib
Abi Fadel wrote:

Hi, i am using PostgreSQL 7.3.2

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

!!!!!!!!!!!!!!!!!!!!!!!!!!!

I think you should replace or clean your keyboard.
At least one key
seems to be sticky.

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

No problem with 7.2.1 and 7.4.1. (don't have a 7.3.2
around).
Both work correctly:

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

--
D i r k F "o r s t e r l i n g
[e-mail] http://r.zorbla.de/
-------------
"A radioactive cat has eighteen
half-lives."

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

__________________________________________________ ____________________

Yahoo! Mail - agora com 100MB de espaço, anti-spam e antivírus grátis!
http://br.info.mail.yahoo.com/

---------------------------(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

[Najib Abi Fadel]

I exactly typed what i sent.
Anyone with Postgresql 7.3.2 PLEASE try it and tell me what happens.

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

7.3.4 seems OK so I assume the guy with the exclamation marks did not
type what he sent:

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

http://www.postgresql.org/docs/faqs/FAQ.html

[Stefan Kaltenbrunner]

Najib Abi Fadel wrote:

I exactly typed what i sent.
Anyone with Postgresql 7.3.2 PLEASE try it and tell me what happens.

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

template1=# select version();
version
------------------------------------------------------------------------
PostgreSQL 7.3.2 on i386-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.3
(1 row)

template1=# SELECT to_char('2005-03-27'::date,'DD/MM/YYYY');
to_char
------------
27/03/2005
(1 row)
Stefan

---------------------------(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

[Robin Ericsson]

On Thu, 2004-07-01 at 14:48, Stefan Kaltenbrunner wrote:

Najib Abi Fadel wrote:

I exactly typed what i sent.
Anyone with Postgresql 7.3.2 PLEASE try it and tell me what happens.

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

template1=# select version();
version
------------------------------------------------------------------------
PostgreSQL 7.3.2 on i386-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.3
(1 row)

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

I had the same problem in php due to DST, but that I guess depends on
the underlaying (buggy?) libc.

using 2005-03-27 12:00 instead of just 2005-03-27 solved the problem for
me.
regards,
Robin
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Tom Lane]

"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*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Maciej Bliziñski]

Najib Abi Fadel wrote:

Hi, i am using PostgreSQL 7.3.2
there's a bug for the date '2005-03-27' !!!!!!!!!!!!!!!!!!!!!!!!!!!
For other dates the function works well !!

I have 7.4.2 and it works well:

maciej@matylda:~$ psql template1
Welcome to psql 7.4.2, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit

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

---------------------------(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

[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: "generalpost" <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*******@postgresql.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*******@postgresql.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*******@postgresql.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,'DD/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(timestamp). 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::timestamptz;
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::timestamptz;
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*******@postgresql.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,text) :
CREATE function to_char(date,text) returns text as ' select
to_char($1::timestamp,$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 YourEmailAddressHere" to ma*******@postgresql.org)