Thread: Bug in function to_char() !!

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
                   r@zorbla.de  http://r.zorbla.de/
                            -------------
            "A radioactive cat has eighteen half-lives."

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  <zakkr@zf.jcu.cz>
 http://home.zf.jcu.cz/~zakkr/

On Thu, Jul 01, 2004 at 09:53:34AM +0200,
 Dirk Försterling <r@zorbla.de> 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)

[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 <r@zorbla.de> 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
>                    r@zorbla.de  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
> majordomo@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/

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:

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

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

"Najib Abi Fadel" <nabifadel@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

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)

[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" <tgl@sss.pgh.pa.us>
To: "Najib Abi Fadel" <nabifadel@usj.edu.lb>
Cc: "generalpost" <pgsql-general@postgresql.org>
Sent: Thursday, July 01, 2004 4:52 PM
Subject: Re: [GENERAL] Bug in function to_char() !!


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

"Najib Abi Fadel" <nabifadel@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

> 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

"Najib Abi Fadel" <nabifadel@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

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