
 Hi,

 I start fix my bug with "YY vs. zero" in formatting.c, and before it
 a see current CVS:

test=# select to_timestamp('10-10-2001', 'MM-DD-YYYY');
      to_timestamp
------------------------
 2001-10-10 00:00:00+02
(1 row)

test=# select to_date('10-10-2001', 'MM-DD-YYYY');
  to_date
------------
 2001-10-09
         ^^
     
 It looks like bug in to_date(), but here is no real code of 
to_date(), because to_date and to_timastamp use same code:

Datum
to_date(PG_FUNCTION_ARGS)
{
        /*
         * Quick hack: since our inputs are just like to_timestamp, hand over
         * the whole input info struct...
         */
        return DirectFunctionCall1(timestamp_date, to_timestamp(fcinfo));
}


 What are you mean?  

            Karel


-- 
 Karel Zak  <zakkr@zf.jcu.cz>
 http://home.zf.jcu.cz/~zakkr/
 
 C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz

Wed, 2 Jan 2002 12:03:24 +0100
Karel Zak <zakkr@zf.jcu.cz>
datetime error?
Karel Zak <zakkr@zf.jcu.cz> writes:
>  I start fix my bug with "YY vs. zero" in formatting.c, and before it
>  a see current CVS:

> test=# select to_timestamp('10-10-2001', 'MM-DD-YYYY');
>       to_timestamp
> ------------------------
>  2001-10-10 00:00:00+02
> (1 row)

> test=# select to_date('10-10-2001', 'MM-DD-YYYY');
>   to_date
> ------------
>  2001-10-09
>          ^^

Hmm, is 2001-10-10 a daylight-savings transition day in your timezone?
Although I thought we'd fixed all those bugs ... and I don't see any
corresponding problem here.

			regards, tom lane

Wed, 02 Jan 2002 11:35:08 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: datetime error? 
On Wed, Jan 02, 2002 at 11:35:08AM -0500, Tom Lane wrote:
> Karel Zak <zakkr@zf.jcu.cz> writes:
> >  I start fix my bug with "YY vs. zero" in formatting.c, and before it
> >  a see current CVS:
> 
> > test=# select to_timestamp('10-10-2001', 'MM-DD-YYYY');
> >       to_timestamp
> > ------------------------
> >  2001-10-10 00:00:00+02
> > (1 row)
> 
> > test=# select to_date('10-10-2001', 'MM-DD-YYYY');
> >   to_date
> > ------------
> >  2001-10-09
> >          ^^
> 
> Hmm, is 2001-10-10 a daylight-savings transition day in your timezone?

 No, it's daylight-savings independent. The interesting thing is that 
 you not see it. I found some things:

 * it not happen for GMT timezone, but for others only (I test 'Japan'
   and 'CET').

 * the difference between to_date and to_timestamp is that to_date use
   the timestamp_date() for conversion. And in the timestamp_date() is
   used timestamp2tm() that output bad 'tm' struct.

   The basic difference is that timestamp2tm() with right output do
   code that call localtime() and timestamp2tm() with bad output skip
   it, because 'tzp' is not defined ("if (tzp != NULL)" in this
   timestamp2tm()).  
 
 
 * and the other thing:

 # select to_date('12-13-1901', 'MM-DD-YYYY');
   to_date
 ------------
  1901-12-13
 (1 row)

 # select to_date('12-14-1901', 'MM-DD-YYYY');
 NOTICE:  timestamp_date: year:1901 mon:12, mday:13
   to_date
 ------------
  1901-12-13
 (1 row)
    
 For 'CET' timezone are all dates before '12-14-1901' right :-)

 IMHO it's timezone problem.

        Karel

-- 
 Karel Zak  <zakkr@zf.jcu.cz>
 http://home.zf.jcu.cz/~zakkr/
 
 C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz

Thu, 3 Jan 2002 12:28:40 +0100
Karel Zak <zakkr@zf.jcu.cz>
Re: datetime error?
> > >  I start fix my bug with "YY vs. zero" in formatting.c, and before it
> > >  a see current CVS:
...
> > Hmm, is 2001-10-10 a daylight-savings transition day in your timezone?
>  No, it's daylight-savings independent. The interesting thing is that
>  you not see it. I found some things:
> 
>  * it not happen for GMT timezone, but for others only (I test 'Japan'
>    and 'CET').
> 
>  * the difference between to_date and to_timestamp is that to_date use
>    the timestamp_date() for conversion. And in the timestamp_date() is
>    used timestamp2tm() that output bad 'tm' struct.
> 
>    The basic difference is that timestamp2tm() with right output do
>    code that call localtime() and timestamp2tm() with bad output skip
>    it, because 'tzp' is not defined ("if (tzp != NULL)" in this
>    timestamp2tm()).

Ah! Have you tried calling timestamptz_date() instead? That one allows
handling time zones internally. Before 7.2, timestamp_date() did handle
time zones, but now we have TIMESTAMP WITHOUT TIME ZONE and TIMESTAMP
WITH TIME ZONE so those internal routines changed out from under you.

                         - Thomas

Thu, 03 Jan 2002 15:56:23 +0000
Thomas Lockhart <lockhart@fourpalms.org>
Re: datetime error?
On Thu, Jan 03, 2002 at 03:56:23PM +0000, Thomas Lockhart wrote:
> 
> >    The basic difference is that timestamp2tm() with right output do
> >    code that call localtime() and timestamp2tm() with bad output skip
> >    it, because 'tzp' is not defined ("if (tzp != NULL)" in this
> >    timestamp2tm()).
> 
> Ah! Have you tried calling timestamptz_date() instead? That one allows
> handling time zones internally. Before 7.2, timestamp_date() did handle
> time zones, but now we have TIMESTAMP WITHOUT TIME ZONE and TIMESTAMP
> WITH TIME ZONE so those internal routines changed out from under you.

 You are right. 

 I don't want send patch with 2 chars. Can you or anyone other fix it
 in src/backend/utils/adt/formatting.c in function to_date (line cca 
 3130) and rename timestamp_date to timestamptz_date?
                                             ^^
 Thanks. The formatting.c is ready to RC1 with this fix.

        Karel
 
-- 
 Karel Zak  <zakkr@zf.jcu.cz>
 http://home.zf.jcu.cz/~zakkr/
 
 C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz

Fri, 4 Jan 2002 11:23:08 +0100
Karel Zak <zakkr@zf.jcu.cz>
Re: datetime error?
...
>  I don't want send patch with 2 chars. Can you or anyone other fix it
>  in src/backend/utils/adt/formatting.c in function to_date (line cca
>  3130) and rename timestamp_date to timestamptz_date?

OK. Sorry for the breakage. Think about a regression test which would
catch this one; I've got a few more input format tests for
date/time/timestamp to add after 7.2 is released to catch more edge
cases in that area...

                     - Thomas

Fri, 04 Jan 2002 15:12:12 +0000
Thomas Lockhart <lockhart@fourpalms.org>
Re: datetime error?
...
>  I don't want send patch with 2 chars. Can you or anyone other fix it
>  in src/backend/utils/adt/formatting.c in function to_date (line cca
>  3130) and rename timestamp_date to timestamptz_date?

Done, committed to CVS, the code builds from a "make clean", and the
regression tests pass.

                         - Thomas

Fri, 04 Jan 2002 15:52:45 +0000
Thomas Lockhart <lockhart@fourpalms.org>
Re: datetime error?