Обсуждение: timestamp field - select error

Hi,

I have a large table with a timestamp field.

select cf03dthr from cf03t where cf03dthr <= '2003-12-31'::timestamp ORDER
BY cf03dthr DESC LIMIT 3
     cf03dthr
---------------------2003-05-27 01:01:012003-05-26 09:50:552003-05-26 09:49:54
(3 rows)

(...) where cf03dthr <= '2003-05-27' (...)
     cf03dthr
---------------------2003-05-26 09:52:282003-05-26 09:50:552003-05-26 09:49:54
(3 rows)

* But if I do: (...) where cf03dthr <= '2003-05-26' (...)
* I get: "ERROR:  convert_timevalue_to_scalar: unsupported type 16976"
* This occurs in any other dates...

If I do: (...) where cf03dthr <= '2003-05-26 09:00:00' (...), for example,
the rows returns OK, but <= '2003-05-26 06:00:00' or many other
combinations give me the same error.


Thank´s for any help.

alexandre

"alexandre :: aldeia digital" <alepaes@aldeiadigital.com.br> writes:
> * But if I do: (...) where cf03dthr <= '2003-05-26' (...)
> * I get: "ERROR:  convert_timevalue_to_scalar: unsupported type 16976"

I could not reproduce this here.  If your column is actually of a domain
over timestamp, then you may need some post-7.3.2 bug fixes; see the
just-released 7.3.3.  Otherwise, please provide a self-contained test
case.
        regards, tom lane

Tom,

Really the field is a Domain (datetime = timestamp without timezone).
And for information, this is a log table. If the criteria is more
"distant" of the most recent registers (50 or 60), the error occurs.

Thank´s Tom.....I will report my tests.

alexandre


> "alexandre :: aldeia digital" <alepaes@aldeiadigital.com.br> writes:
>> * But if I do: (...) where cf03dthr <= '2003-05-26' (...)
>> * I get: "ERROR:  convert_timevalue_to_scalar: unsupported type 16976"
>
> I could not reproduce this here.  If your column is actually of a domain
> over timestamp, then you may need some post-7.3.2 bug fixes; see the
> just-released 7.3.3.  Otherwise, please provide a self-contained test
> case.
>
>             regards, tom lane
>

"alexandre :: aldeia digital" <alepaes@aldeiadigital.com.br> writes:
> Really the field is a Domain (datetime = timestamp without timezone).

Ah.  Okay, then the reason I don't see it is I have this 7.3.3 patch:

2003-03-22 20:49  tgl
* src/backend/: optimizer/path/indxpath.c, utils/adt/selfuncs.c(REL7_3_STABLE): Tweak selectivity and related routines
tocopewith domains.  Per report from Andreas Pflug.
 
        regards, tom lane