Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

phoenix date datatype and date functions givings wrong result

phoenix date datatype and date functions givings wrong result

created a phoenix table by mentioning one of the field datatype as date and inserting data using upsert command .

It is giving the exact inserted records while running simple select query on date field where as giving one day less results for few records while using date functions such as month() , dayofmonth() ...etc .

Here , We are facing this issue if the time of date field is between 00:00:01 - 04:59:59 . for other times , date functions are giving right results.

create table "Phoenix_table" ( Server VARCHAR,"station" VARCHAR, "event" DATE not null , CONSTRAINT CTKEY PRIMARY KEY (Server, "event"));

upsert into "Phoenix_table"(Server,"station","event") values('s1','st1','2016-12-01 00:00:59');

upsert into "Phoenix_table"(Server,"station","event") values('s1','st1','2016-12-01 04:59:59');

upsert into "Phoenix_table"(Server,"station","event") values('s1','st1','2016-12-01 05:00:01');

upsert into "Phoenix_table"(Server,"station","event") values('s1','st1','2016-12-01 13:00:01');

select "event",year("event"),month("event"),dayofmonth("event"),hour("event"),minute("event"),second("event") from "Phoenix_table";

for the above select query first 2 inserted records are giving wrong result where as last 2 inserted records are giving expected results .

Please find the attachment for more clarity .

11236-phoenix-date.png

4 REPLIES 4
Highlighted

Re: phoenix date datatype and date functions givings wrong result

Are you using HDP? If so, what version of HDP?

Otherwise if you're using Apache Phoenix, what version?

Highlighted

Re: phoenix date datatype and date functions givings wrong result

We are using HDP2.5 and Phoenix4.7 . FYI - I have done further investigation . looks like it is offset difference . How can i adjust offset while loading the date data in table ? Thank you

Highlighted

Re: phoenix date datatype and date functions givings wrong result

As said in my last comment, you can adjust the timezone offset by setting "phoenix.query.dateFormatTimeZone" property in your hbase-site.xml of the client.

https://phoenix.apache.org/tuning.html

Highlighted

Re: phoenix date datatype and date functions givings wrong result

You might be hitting similar problem stated in https://issues.apache.org/jira/browse/PHOENIX-3220

setting phoenix.query.dateFormatTimeZone to your local timezone can be workaround for now.

Don't have an account?
Coming from Hortonworks? Activate your account here