Horton

Thread

  1. BUG #1630: Wrong conversion in to_date() function. See example.

    Ariel Carna <acarna@tarifar.com> — 2005-04-26T22:48:12Z

    The following bug has been logged online:

Bug reference:      1630
Logged by:          Ariel E. Carn/Elizabeth Sosa
Email address:      acarna@tarifar.com
PostgreSQL version: 7.3.8/7.4.6
Operating system:   SuSE Linux
Description:        Wrong conversion in to_date() function. See example.
Details: 

Case PgSQL 7.3.8/SuSE Linux 8.2 (i586)
======================================
PROD=# select to_date('2005-02-32', 'YYYY-MM-DD');
  to_date
------------
 2005-03-04
(1 row)

PROD=# select version();
                                                 version
----------------------------------------------------------------------------
-----------------------------
 PostgreSQL 7.3.8 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3
20030226 (prerelease) (SuSE Linux)
(1 row)


Case PgSQL 7.4.6/SuSE Linux 9.2
(i586)======================================
PROD=> select to_date('2005-02-32', 'YYYY-MM-DD');
  to_date
------------
 04-03-2005
(1 row)

PROD=> select version();
                                           version
----------------------------------------------------------------------------
-----------------
 PostgreSQL 7.4.6 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.4 (pre
3.3.5 20040809)
(1 row)

  2. Re: BUG #1630: Wrong conversion in to_date() function. See example.

    Michael Fuhr <mike@fuhr.org> — 2005-04-27T14:22:07Z

    On Tue, Apr 26, 2005 at 11:48:12PM +0100, Ariel E. Carná/Elizabeth Sosa wrote:
> 
> Case PgSQL 7.3.8/SuSE Linux 8.2 (i586)
> ======================================
> PROD=# select to_date('2005-02-32', 'YYYY-MM-DD');
>   to_date
> ------------
>  2005-03-04
> (1 row)
> 
> Case PgSQL 7.4.6/SuSE Linux 9.2
> (i586)======================================
> PROD=> select to_date('2005-02-32', 'YYYY-MM-DD');
>   to_date
> ------------
>  04-03-2005
> (1 row)

What bug are you reporting?  These are the same date displayed in
different formats -- are you familiar with the DateStyle setting?

http://www.postgresql.org/docs/7.3/interactive/datatype-datetime.html#DATATYPE-DATETIME-OUTPUT
http://www.postgresql.org/docs/7.4/interactive/datatype-datetime.html#DATATYPE-DATETIME-OUTPUT

test=> SELECT version();
                                  version                                  
---------------------------------------------------------------------------
 PostgreSQL 7.4.7 on sparc-sun-solaris2.9, compiled by GCC gcc (GCC) 3.4.2
(1 row)

test=> SET DateStyle TO 'ISO,MDY';
SET
test=> SELECT to_date('2005-02-32', 'YYYY-MM-DD');
  to_date   
------------
 2005-03-04
(1 row)

test=> SET DateStyle TO 'Postgres,DMY';
SET
test=> SELECT to_date('2005-02-32', 'YYYY-MM-DD');
  to_date   
------------
 04-03-2005
(1 row)

If anything I'd expect 2005-02-32 to be rejected as invalid, but I
don't know the history or rationale behind to_date's behavior.

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/

  3. Re: BUG #1630: Wrong conversion in to_date() function. See example.

    Tom Lane <tgl@sss.pgh.pa.us> — 2005-04-27T14:45:42Z

    "Ariel E. Carn/Elizabeth Sosa" <acarna@tarifar.com> writes:
> PROD=# select to_date('2005-02-32', 'YYYY-MM-DD');
>   to_date
> ------------
>  2005-03-04
> (1 row)

I'm not convinced that's a bug --- most implementations of the Unix
mktime function will handle out-of-range day numbers like that.

			regards, tom lane

  4. Re: BUG #1630: Wrong conversion in to_date() function. See example.

    Juan Miguel Paredes <juan.paredes@gmail.com> — 2005-04-27T14:50:23Z

    Even javascript handles those dates the same way...

 On 4/27/05, Tom Lane <tgl@sss.pgh.pa.us> wrote: 
> 
> "Ariel E. Carná/Elizabeth Sosa" <acarna@tarifar.com> writes:
> > PROD=# select to_date('2005-02-32', 'YYYY-MM-DD');
> > to_date
> > ------------
> > 2005-03-04
> > (1 row)
> 
> I'm not convinced that's a bug --- most implementations of the Unix
> mktime function will handle out-of-range day numbers like that.
> 
> regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
> http://www.postgresql.org/docs/faq
>

  5. Re: BUG #1630: Wrong conversion in to_date() function. See example.

    Tom Lane <tgl@sss.pgh.pa.us> — 2005-04-27T14:53:52Z

    Michael Fuhr <mike@fuhr.org> writes:
> If anything I'd expect 2005-02-32 to be rejected as invalid, but I
> don't know the history or rationale behind to_date's behavior.

It is rejected by the standard date input converter:

regression=# select '2005-02-32'::date;
ERROR:  date/time field value out of range: "2005-02-32"
HINT:  Perhaps you need a different "datestyle" setting.

However we consider that to_date() exists to be Oracle compatible,
and so I would regard this as a bug if and only if Oracle does
something different with the same input.  Anyone know?

			regards, tom lane

  6. Re: BUG #1630: Wrong conversion in to_date() function. See example.

    Ariel Carna <acarna@tarifar.com> — 2005-04-27T15:06:13Z

    With Oracle (at least since 6.x version) this conversion is wrong.


Atentamente , 

Ariel Carná




-----Mensaje original-----
De: Tom Lane [mailto:tgl@sss.pgh.pa.us] 
Enviado el: Miércoles, 27 de Abril de 2005 11:54 a.m.
Para: Michael Fuhr
CC: Ariel E. Carná/Elizabeth Sosa; pgsql-bugs@postgresql.org
Asunto: Re: [BUGS] BUG #1630: Wrong conversion in to_date() function. See
example. 

Michael Fuhr <mike@fuhr.org> writes:
> If anything I'd expect 2005-02-32 to be rejected as invalid, but I
> don't know the history or rationale behind to_date's behavior.

It is rejected by the standard date input converter:

regression=# select '2005-02-32'::date;
ERROR:  date/time field value out of range: "2005-02-32"
HINT:  Perhaps you need a different "datestyle" setting.

However we consider that to_date() exists to be Oracle compatible,
and so I would regard this as a bug if and only if Oracle does
something different with the same input.  Anyone know?

			regards, tom lane

  7. Re: BUG #1630: Wrong conversion in to_date() function. See

    Karel Zak <zakkr@zf.jcu.cz> — 2005-05-02T20:33:40Z

    On Wed, 2005-04-27 at 10:53 -0400, Tom Lane wrote:
> Michael Fuhr <mike@fuhr.org> writes:
> > If anything I'd expect 2005-02-32 to be rejected as invalid, but I
> > don't know the history or rationale behind to_date's behavior.
> 
> It is rejected by the standard date input converter:
> 
> regression=# select '2005-02-32'::date;
> ERROR:  date/time field value out of range: "2005-02-32"
> HINT:  Perhaps you need a different "datestyle" setting.
> 
> However we consider that to_date() exists to be Oracle compatible,
> and so I would regard this as a bug if and only if Oracle does
> something different with the same input.  Anyone know?

It's "almost" bug. And it's in TODO (but it's really long todo..:-( I
think to_date/timestamp() should be more pedantic.

	Karel

PS. for volunteers for the work on new generation of to_char/date():
http://people.redhat.com/kzak/libfmt/libfmt-0.2-03262005.tar.gz

-- 
Karel Zak <zakkr@zf.jcu.cz>