Postgres Pro
Downloads
Home   >   mailing lists

Thread: date style bug

date style bug

From
Felipe Alvarez Harnecker
Date:
Hi,

I'm having this bug ramdonly, some time with a "vacuum analyze" goes
away but not always

here the info:



obelix3=> select version();
                            version
---------------------------------------------------------------
 PostgreSQL 7.0.3 on i686-pc-linux-gnu, compiled by gcc 2.95.2
(1 row)


obelix3=> set datestyle=postgres,us;
SET VARIABLE

obelix3=> select * from orden_visita where fecha_emision <= 'today'::date;
 numero | corredor | ejecutivo |  remoto   |  cliente  |  retira   | tipo_operac
ion | fecha_emision
--------+----------+-----------+-----------+-----------+-----------+------------
----+---------------
    292 | 78626380 |   6607775 | 127.0.0.1 |   8779524 |   8779524 |
Venta

QUERY RUNS OK.

obelix3=> set datestyle=postgres,euro;
SET VARIABLE


obelix3=> select * from orden_visita where fecha_emision <= 'today'::date;
ERROR:  Bad date external representation '07-17-2001'


BUG.

Please help me I'm near suicide ...

Thanx.

--
______________________________________________________

Felipe Alvarez Harnecker.  QlSoftware.

Tels. 665.99.41 - 09.874.60.17
e-mail: felipe.alvarez@qlsoft.cl

http://qlsoft.cl/
http://ql.cl/
______________________________________________________

Re: date style bug

From
Tom Lane
Date:
Felipe Alvarez Harnecker <felipe@qlsoft.cl> writes:
> obelix3=> set datestyle=postgres,euro;
> SET VARIABLE

> obelix3=> select * from orden_visita where fecha_emision <= 'today'::date;
> ERROR:  Bad date external representation '07-17-2001'

Since '07-17-2001' is *not* a valid date in Euro convention
(dd-mm-yyyy), I don't see any bug here.

I take it fecha_emision is a text or varchar field that your query is
casting to date on-the-fly.  Perhaps you'd be better advised to use
datatype date (or timestamp) for the field in the first place.

            regards, tom lane

Re: date style bug

From
Thomas Lockhart
Date:
> I'm having this bug ramdonly, some time with a "vacuum analyze" goes
> away but not always

Likely not related at all.

> obelix3=> set datestyle=postgres,us;
> SET VARIABLE
> obelix3=> select * from orden_visita where fecha_emision <= 'today'::date;
>  numero | corredor | ejecutivo |  remoto   |  cliente  |  retira   | tipo_operac
> ion | fecha_emision
> --------+----------+-----------+-----------+-----------+-----------+------------
> ----+---------------
>     292 | 78626380 |   6607775 | 127.0.0.1 |   8779524 |   8779524 |
> Venta
> QUERY RUNS OK.

What is the value of the fecha_emision column? What is the schema?
>
> obelix3=> set datestyle=postgres,euro;
> obelix3=> select * from orden_visita where fecha_emision <= 'today'::date;
> ERROR:  Bad date external representation '07-17-2001'

What is the schema? I'll guess that fecha_emision is not actually a date
type (either date or timestamp) but rather a text field. That is the
only way I can think of to provoke an "external representation" error.

More details please.

                      - Thomas

Re: date style bug

From
Felipe Alvarez Harnecker
Date:
Thomas Lockhart writes:
 > > I'm having this bug ramdonly, some time with a "vacuum analyze" goes
 > > away but not always
 >
 > Likely not related at all.
 >
 > > obelix3=> set datestyle=postgres,us;
 > > SET VARIABLE
 > > obelix3=> select * from orden_visita where fecha_emision <= 'today'::date;
 > >  numero | corredor | ejecutivo |  remoto   |  cliente  |  retira   | tipo_operac
 > > ion | fecha_emision
 > > --------+----------+-----------+-----------+-----------+-----------+------------
 > > ----+---------------
 > >     292 | 78626380 |   6607775 | 127.0.0.1 |   8779524 |   8779524 |
 > > Venta
 > > QUERY RUNS OK.
 >
 > What is the value of the fecha_emision column? What is the schema?
 > >
 > > obelix3=> set datestyle=postgres,euro;
 > > obelix3=> select * from orden_visita where fecha_emision <= 'today'::date;
 > > ERROR:  Bad date external representation '07-17-2001'
 >
 > What is the schema? I'll guess that fecha_emision is not actually a date
 > type (either date or timestamp) but rather a text field. That is the
 > only way I can think of to provoke an "external representation" error.
 >
 > More details please.
 >
 >                       - Thomas
 >

Here is it:
-------------


obelix3=> \d orden_visita
                                 Table "orden_visita"
   Attribute    |  Type   |                         Modifier
----------------+---------+-----------------------------------------------------------
 numero         | integer | not null default nextval('orden_visita_numero_seq'::text)
 corredor       | integer | not null
 ejecutivo      | integer | not null
 remoto         | inet    | not null
 cliente        | integer | not null
 retira         | integer | not null
 tipo_operacion | text    | not null default 'Arriendo'
 fecha_emision  | date    | default date("timestamp"('now'::text))
Index: orden_visita_pkey

--
______________________________________________________

Felipe Alvarez Harnecker.  QlSoftware.

Tels. 665.99.41 - 09.874.60.17
e-mail: felipe.alvarez@qlsoft.cl

http://qlsoft.cl/
http://ql.cl/
______________________________________________________