You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@phoenix.apache.org by lk_phoenix <lk...@163.com> on 2016/12/02 05:43:31 UTC
how to filter date type column ?
hi,all:
I have a table with a column as date type, I try to use it as a where condition: but it was not work.
select date,TO_DATE('2015-06-01','yyyy-MM-dd') ,date=TO_DATE('2015-06-01','yyyy-MM-dd')from FARM_PRODUCT_PRICE limit 100;
and ,I can do it like this :
select * from FARM_PRODUCT_PRICE where TO_CHAR(date, 'yyyy-MM-dd') = '2016-09-29' ;
but , it will not use index if I create index like this :
CREATE INDEX idx_fpp ON FARM_PRODUCT_PRICE (TO_CHAR(date, 'yyyy-MM-dd'));
thanks for any help.
2016-12-02
lk_phoenix
Re: how to filter date type column ?
Posted by Ciureanu Constantin <ci...@gmail.com>.
Sorry, saw you already tried with the condition as part of both the SELECT
and WHERE .
The Date field has probably some time and then the equal doesn't work.
I recommend getting the TIMESTAMP value out of it and take a look.
The best option for HBase Scan to work fast is to store the Dates as
timestamps (long)
2016-12-02 9:11 GMT+01:00 Ciureanu Constantin <ciureanu.constantin@gmail.com
>:
> Try using WHERE clause...
>
> ... FROM FARM_PRODUCT_PRICE
> WHERE date=TO_DATE('2015-06-01','yyyy-MM-dd')
> LIMIT 100;
>
> 2016-12-02 6:43 GMT+01:00 lk_phoenix <lk...@163.com>:
>
>> hi,all:
>> I have a table with a column as date type, I try to use it as a where
>> condition: but it was not work.
>>
>> select date,TO_DATE('2015-06-01','yyyy-MM-dd')
>> ,date=TO_DATE('2015-06-01','yyyy-MM-dd')from FARM_PRODUCT_PRICE limit
>> 100;
>>
>>
>> and ,I can do it like this :
>> select * from FARM_PRODUCT_PRICE where TO_CHAR(date, 'yyyy-MM-dd') =
>> '2016-09-29' ;
>> but , it will not use index if I create index like this :
>> CREATE INDEX idx_fpp ON FARM_PRODUCT_PRICE (TO_CHAR(date, 'yyyy-MM-dd'));
>>
>>
>>
>> thanks for any help.
>>
>> 2016-12-02
>> ------------------------------
>> lk_phoenix
>>
>
>
Re: how to filter date type column ?
Posted by Ciureanu Constantin <ci...@gmail.com>.
Try using WHERE clause...
... FROM FARM_PRODUCT_PRICE
WHERE date=TO_DATE('2015-06-01','yyyy-MM-dd')
LIMIT 100;
2016-12-02 6:43 GMT+01:00 lk_phoenix <lk...@163.com>:
> hi,all:
> I have a table with a column as date type, I try to use it as a where
> condition: but it was not work.
>
> select date,TO_DATE('2015-06-01','yyyy-MM-dd')
> ,date=TO_DATE('2015-06-01','yyyy-MM-dd')from FARM_PRODUCT_PRICE limit 100;
>
>
> and ,I can do it like this :
> select * from FARM_PRODUCT_PRICE where TO_CHAR(date, 'yyyy-MM-dd') =
> '2016-09-29' ;
> but , it will not use index if I create index like this :
> CREATE INDEX idx_fpp ON FARM_PRODUCT_PRICE (TO_CHAR(date, 'yyyy-MM-dd'));
>
>
>
> thanks for any help.
>
> 2016-12-02
> ------------------------------
> lk_phoenix
>