phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Ankit Singhal <ankitsingha...@gmail.com>
Subject Re: strange behavior with DATE columns
Date Sat, 30 Jan 2016 07:06:38 GMT
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 <Afshin.Moazami@guavus.com>
wrote:

> Do you consider adjusting timezones when using JDBC?
>
> On Jan 8, 2016, at 3:01 AM, Thomas Decaux <ebuildy@gmail.com> wrote:
>
> What about Phoenix shell? I don't see this problem with Datagrip or
> Phoenix shell.
>
> 2016-01-07 20:53 GMT+01:00 <kannan.ramanathan@barclays.com>:
>
>> 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.
>>
>> _______________________________________________
>>
>
>
>

Mime
View raw message