Support Questions
Find answers, ask questions, and share your expertise

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?