Hi Poonam, You should consider storing date fields in DATE/TIME datatypes instead of Char(22).
On Friday, October 10, 2014, Poonam Ligade <[email protected]> wrote: > Hi, > > I have phoenix table with below schema, > CREATE TABLE IF NOT EXISTS TEST ( > ID BIGINT NOT NULL, > Date1 char(22) NOT NULL, > StID INTEGER NOT NULL, > .... > .......... > CONSTRAINT PK PRIMARY KEY (ID,Date1,StID) > ); > > I have dates in below format > 01/02/2013 12:00:00 AM > 02/07/2013 12:00:00 AM > 03/25/2013 12:00:00 AM > 4/12/2013 12:00:00 AM > 5/16/2013 12:00:00 AM > > I have over 200+milion rows of size 110GB in this table. > > I frequently need to retrieve rows based on Date1. > To get data for January month, I am doing > select * from TEST where TO_DATE(Date1,'MM/dd/yyyy') < > TO_DATE('02/01/2013','MM/dd/yyyy'); > or > select * from TEST where substr(Date1,0,2)='01'; > > or to get week range > select * from TEST where TO_DATE(Date1,'MM/dd/yyyy') < > TO_DATE('03/18/2013','MM/dd/yyyy') and TO_DATE(Date1,'MM/dd/yyyy') > > TO_DATE('03/10/2013','MM/dd/yyyy') > > But these queries are slow, > Can you suggest more performant queries, to fetch data based on date range > in phoenix. > > I am using Hortonworks hadoop 2.1, hbase-0.98 and phoenix 4.1 > > Regards, > Poonam. >
