As Afshin also said, You need to adjust your timezone with
phoenix.query.dateFormatTimeZone
https://phoenix.apache.org/tuning.html
<property>
<name>phoenix.query.dateFormatTimeZone</name>
<value>IST</value>
</property>
for eg:-
*upsert like this:*-
jdbc:phoenix:localhost> UPSERT INTO DESTINATION_METRICS_TABLE VALUES
(to_date('2015-09-12 22:02:11'), 'timezone unset', 1);
*With property set(which is in IST):-*
0: jdbc:phoenix:localhost> select * from DESTINATION_METRICS_TABLE;
+---------------------+-----------------+------------------------------------------+
| CREATED_DATE | METRIC_ID | METRIC_VALUE
|
+---------------------+-----------------+------------------------------------------+
| 2015-09-12 | timezone set | 1
|
+---------------------+-----------------+—————————————————————+
*With property unset(date passed in upsert will be considered to be of
default timezone i.e GMT and convert it to local timezone IST+5:30 while
storing in hbase):-*
0: jdbc:phoenix:localhost> select * from DESTINATION_METRICS_TABLE;
+---------------------+-----------------+------------------------------------------+
| CREATED_DATE | METRIC_ID | METRIC_VALUE
|
+---------------------+-----------------+------------------------------------------+
| 2015-09-13 | timezone unset | 1
|
+---------------------+-----------------+------------------------------------------+
On Fri, Jan 8, 2016 at 8:15 PM, Afshin Moazami <[email protected]>
wrote:
> Do you consider adjusting timezones when using JDBC?
>
> On Jan 8, 2016, at 3:01 AM, Thomas Decaux <[email protected]> wrote:
>
> What about Phoenix shell? I don't see this problem with Datagrip or
> Phoenix shell.
>
> 2016-01-07 20:53 GMT+01:00 <[email protected]>:
>
>> Hello,
>>
>>
>>
>> I am having a DATE column in Phoenix DB. Through SQuirreL SQL client or
>> JDBC, when I do an upsert date as string, the resultant date is always one
>> day less. For ex all the below statements (with any date format in TO_DATE
>> function) yield the same result (i.e., instead of 15th it’s 14th of July):
>>
>>
>>
>> COLUMN TYPE
>>
>> ======= ====
>>
>> COL1 VARCHAR
>>
>> COL3 DATE
>>
>>
>>
>> Queries:
>>
>> ======
>>
>> UPSERT INTO TEST VALUES ('5', TO_DATE('2016-07-15', 'yyyy-MM-dd'))
>>
>> UPSERT INTO TEST VALUES ('5', '07/15/2016')
>>
>>
>>
>> SELECT * FROM TEST
>>
>>
>>
>> Result:
>>
>> ======
>>
>> 5 2016-07-14
>>
>>
>>
>>
>>
>> If I use JDBC PreparedStatement.setDate(…), it works as expected.
>>
>>
>>
>> Any ideas?
>>
>>
>>
>> Thanks
>>
>> Kannan.
>>
>>
>>
>> _______________________________________________
>>
>> This message is for information purposes only, it is not a
>> recommendation, advice, offer or solicitation to buy or sell a product or
>> service nor an official confirmation of any transaction. It is directed at
>> persons who are professionals and is not intended for retail customer use.
>> Intended for recipient only. This message is subject to the terms at:
>> www.barclays.com/emaildisclaimer.
>>
>> For important disclosures, please see:
>> www.barclays.com/salesandtradingdisclaimer regarding market commentary
>> from Barclays Sales and/or Trading, who are active market participants; and
>> in respect of Barclays Research, including disclosures relating to specific
>> issuers, please see http://publicresearch.barclays.com.
>>
>> _______________________________________________
>>
>
>
>
