You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@kylin.apache.org by 张 佑铖 <ed...@hotmail.com> on 2018/02/04 13:42:03 UTC
答复: Timezone offset with java.sql.Date with kylin jdbc
Hi Shaofeng,
Thank you for reply, but a little strange is that when I use in previous version 2.1.0 and not encounter this problem.
I will check it later.
Thanks again
________________________________
发件人: ShaoFeng Shi <sh...@apache.org>
发送时间: 2018年2月4日 21:03
收件人: dev
主题: Re: Timezone offset with java.sql.Date with kylin jdbc
Check whether this can help:
http://apache-kylin.74782.x6.nabble.com/JDBC-query-result-Date-column-get-wrong-value-td5370.html
2018-02-04 19:29 GMT+08:00 张 佑铖 <ed...@hotmail.com>:
> Hi kylin team,
>
> Single problem, column with type Date values '2018-02-02' in hive. Query
> in kylin sight is okay return value with '2018-02-02'.
> But when I used jdbc it returns '2018-02-01'.
>
> Apparently it is a problem with timezone.
> 1. Check the configuration first
> kylin.web.timezone=GMT+8
> user.timezone = Asia/Shanghai
> seems no problem
> 2. Check client
> GMT+8
> I also add code "System.setProperty("user.timezone","GMT +08");" to
> enforce the same timezone with server
> 3. Follow the code
> after request query, I check the content of response, still '2018-02-02'
> after convert resultset, value is still '2018-02-02'
> and finally rs.getDate("")
>
> public Date getDate(Calendar calendar) throws SQLException {
> Date date = (Date)this.getObject();
> if (date == null) {
> return null;
> } else {
> if (calendar != null) {
> long v = date.getTime();
> v -= (long)calendar.getTimeZone().getOffset(v);
> date = new Date(v);
> }
>
> return date;
> }
> }
>
> localCalendar of cource with timezone GMT+8, so after the calculation of
> offset '2018-02-02' turn out to be '2018-02-01'
>
> So I am confused, according to the above if I set locale to UTC I will get
> the right value, but it is unreasonable.
>
> Is there any configuration or some params for jdbc url I missed?
>
> One thing should be mentioned, server of kylin was set UTC before, I
> changed locale to Asia/Shanghai and restart kylin, refresh the cube. I
> don't know if that has impact?
>
>
> Thank you
>
>
>
>
>
--
Best regards,
Shaofeng Shi 史少锋
答复: 答复: Timezone offset with java.sql.Date with kylin jdbc
Posted by 张 佑铖 <ed...@hotmail.com>.
Finally after watch document of calcite, I changed connect url from "jdbc:kylin//" --> "jdbc:kylin:timeZone=UTC//"
maybe not a good solution, welcome suggestions.
Thanks
best regards
________________________________
发件人: 张 佑铖 <ed...@hotmail.com>
发送时间: 2018年2月5日 10:58
收件人: dev@kylin.apache.org
主题: 答复: 答复: Timezone offset with java.sql.Date with kylin jdbc
I am sorry it's my mistake, In previous version, I used 3 int fields year,month,day separately in the model so it was okay.
Actually in my case the date column is just a constant field of a dimension table and just with year,month,day without time.
I think it is not wise to find all the case in the code and add specific timezone UTC.
Maybe I should use string or like previously 3 int fields year,month,day separately to prevent timezone-shifting problem globally?
Maybe there is other way to walk around this timezone-shifting problem?
________________________________
发件人: Billy Liu <bi...@apache.org>
发送时间: 2018年2月4日 22:09
收件人: dev
主题: Re: 答复: Timezone offset with java.sql.Date with kylin jdbc
If you think there is something wrong between different versions, new
test cases on the timezone issue should be added. Then we could
guarantee the regression test. Patch or PR is welcomed.
2018-02-04 21:42 GMT+08:00 张 佑铖 <ed...@hotmail.com>:
> Hi Shaofeng,
>
> Thank you for reply, but a little strange is that when I use in previous version 2.1.0 and not encounter this problem.
> I will check it later.
>
> Thanks again
>
>
> ________________________________
> 发件人: ShaoFeng Shi <sh...@apache.org>
> 发送时间: 2018年2月4日 21:03
> 收件人: dev
> 主题: Re: Timezone offset with java.sql.Date with kylin jdbc
>
> Check whether this can help:
> http://apache-kylin.74782.x6.nabble.com/JDBC-query-result-Date-column-get-wrong-value-td5370.html
>
> 2018-02-04 19:29 GMT+08:00 张 佑铖 <ed...@hotmail.com>:
>
>> Hi kylin team,
>>
>> Single problem, column with type Date values '2018-02-02' in hive. Query
>> in kylin sight is okay return value with '2018-02-02'.
>> But when I used jdbc it returns '2018-02-01'.
>>
>> Apparently it is a problem with timezone.
>> 1. Check the configuration first
>> kylin.web.timezone=GMT+8
>> user.timezone = Asia/Shanghai
>> seems no problem
>> 2. Check client
>> GMT+8
>> I also add code "System.setProperty("user.timezone","GMT +08");" to
>> enforce the same timezone with server
>> 3. Follow the code
>> after request query, I check the content of response, still '2018-02-02'
>> after convert resultset, value is still '2018-02-02'
>> and finally rs.getDate("")
>>
>> public Date getDate(Calendar calendar) throws SQLException {
>> Date date = (Date)this.getObject();
>> if (date == null) {
>> return null;
>> } else {
>> if (calendar != null) {
>> long v = date.getTime();
>> v -= (long)calendar.getTimeZone().getOffset(v);
>> date = new Date(v);
>> }
>>
>> return date;
>> }
>> }
>>
>> localCalendar of cource with timezone GMT+8, so after the calculation of
>> offset '2018-02-02' turn out to be '2018-02-01'
>>
>> So I am confused, according to the above if I set locale to UTC I will get
>> the right value, but it is unreasonable.
>>
>> Is there any configuration or some params for jdbc url I missed?
>>
>> One thing should be mentioned, server of kylin was set UTC before, I
>> changed locale to Asia/Shanghai and restart kylin, refresh the cube. I
>> don't know if that has impact?
>>
>>
>> Thank you
>>
>>
>>
>>
>>
>
>
> --
> Best regards,
>
> Shaofeng Shi 史少锋
答复: 答复: Timezone offset with java.sql.Date with kylin jdbc
Posted by 张 佑铖 <ed...@hotmail.com>.
I am sorry it's my mistake, In previous version, I used 3 int fields year,month,day separately in the model so it was okay.
Actually in my case the date column is just a constant field of a dimension table and just with year,month,day without time.
I think it is not wise to find all the case in the code and add specific timezone UTC.
Maybe I should use string or like previously 3 int fields year,month,day separately to prevent timezone-shifting problem globally?
Maybe there is other way to walk around this timezone-shifting problem?
________________________________
发件人: Billy Liu <bi...@apache.org>
发送时间: 2018年2月4日 22:09
收件人: dev
主题: Re: 答复: Timezone offset with java.sql.Date with kylin jdbc
If you think there is something wrong between different versions, new
test cases on the timezone issue should be added. Then we could
guarantee the regression test. Patch or PR is welcomed.
2018-02-04 21:42 GMT+08:00 张 佑铖 <ed...@hotmail.com>:
> Hi Shaofeng,
>
> Thank you for reply, but a little strange is that when I use in previous version 2.1.0 and not encounter this problem.
> I will check it later.
>
> Thanks again
>
>
> ________________________________
> 发件人: ShaoFeng Shi <sh...@apache.org>
> 发送时间: 2018年2月4日 21:03
> 收件人: dev
> 主题: Re: Timezone offset with java.sql.Date with kylin jdbc
>
> Check whether this can help:
> http://apache-kylin.74782.x6.nabble.com/JDBC-query-result-Date-column-get-wrong-value-td5370.html
>
> 2018-02-04 19:29 GMT+08:00 张 佑铖 <ed...@hotmail.com>:
>
>> Hi kylin team,
>>
>> Single problem, column with type Date values '2018-02-02' in hive. Query
>> in kylin sight is okay return value with '2018-02-02'.
>> But when I used jdbc it returns '2018-02-01'.
>>
>> Apparently it is a problem with timezone.
>> 1. Check the configuration first
>> kylin.web.timezone=GMT+8
>> user.timezone = Asia/Shanghai
>> seems no problem
>> 2. Check client
>> GMT+8
>> I also add code "System.setProperty("user.timezone","GMT +08");" to
>> enforce the same timezone with server
>> 3. Follow the code
>> after request query, I check the content of response, still '2018-02-02'
>> after convert resultset, value is still '2018-02-02'
>> and finally rs.getDate("")
>>
>> public Date getDate(Calendar calendar) throws SQLException {
>> Date date = (Date)this.getObject();
>> if (date == null) {
>> return null;
>> } else {
>> if (calendar != null) {
>> long v = date.getTime();
>> v -= (long)calendar.getTimeZone().getOffset(v);
>> date = new Date(v);
>> }
>>
>> return date;
>> }
>> }
>>
>> localCalendar of cource with timezone GMT+8, so after the calculation of
>> offset '2018-02-02' turn out to be '2018-02-01'
>>
>> So I am confused, according to the above if I set locale to UTC I will get
>> the right value, but it is unreasonable.
>>
>> Is there any configuration or some params for jdbc url I missed?
>>
>> One thing should be mentioned, server of kylin was set UTC before, I
>> changed locale to Asia/Shanghai and restart kylin, refresh the cube. I
>> don't know if that has impact?
>>
>>
>> Thank you
>>
>>
>>
>>
>>
>
>
> --
> Best regards,
>
> Shaofeng Shi 史少锋
Re: 答复: Timezone offset with java.sql.Date with kylin jdbc
Posted by Billy Liu <bi...@apache.org>.
If you think there is something wrong between different versions, new
test cases on the timezone issue should be added. Then we could
guarantee the regression test. Patch or PR is welcomed.
2018-02-04 21:42 GMT+08:00 张 佑铖 <ed...@hotmail.com>:
> Hi Shaofeng,
>
> Thank you for reply, but a little strange is that when I use in previous version 2.1.0 and not encounter this problem.
> I will check it later.
>
> Thanks again
>
>
> ________________________________
> 发件人: ShaoFeng Shi <sh...@apache.org>
> 发送时间: 2018年2月4日 21:03
> 收件人: dev
> 主题: Re: Timezone offset with java.sql.Date with kylin jdbc
>
> Check whether this can help:
> http://apache-kylin.74782.x6.nabble.com/JDBC-query-result-Date-column-get-wrong-value-td5370.html
>
> 2018-02-04 19:29 GMT+08:00 张 佑铖 <ed...@hotmail.com>:
>
>> Hi kylin team,
>>
>> Single problem, column with type Date values '2018-02-02' in hive. Query
>> in kylin sight is okay return value with '2018-02-02'.
>> But when I used jdbc it returns '2018-02-01'.
>>
>> Apparently it is a problem with timezone.
>> 1. Check the configuration first
>> kylin.web.timezone=GMT+8
>> user.timezone = Asia/Shanghai
>> seems no problem
>> 2. Check client
>> GMT+8
>> I also add code "System.setProperty("user.timezone","GMT +08");" to
>> enforce the same timezone with server
>> 3. Follow the code
>> after request query, I check the content of response, still '2018-02-02'
>> after convert resultset, value is still '2018-02-02'
>> and finally rs.getDate("")
>>
>> public Date getDate(Calendar calendar) throws SQLException {
>> Date date = (Date)this.getObject();
>> if (date == null) {
>> return null;
>> } else {
>> if (calendar != null) {
>> long v = date.getTime();
>> v -= (long)calendar.getTimeZone().getOffset(v);
>> date = new Date(v);
>> }
>>
>> return date;
>> }
>> }
>>
>> localCalendar of cource with timezone GMT+8, so after the calculation of
>> offset '2018-02-02' turn out to be '2018-02-01'
>>
>> So I am confused, according to the above if I set locale to UTC I will get
>> the right value, but it is unreasonable.
>>
>> Is there any configuration or some params for jdbc url I missed?
>>
>> One thing should be mentioned, server of kylin was set UTC before, I
>> changed locale to Asia/Shanghai and restart kylin, refresh the cube. I
>> don't know if that has impact?
>>
>>
>> Thank you
>>
>>
>>
>>
>>
>
>
> --
> Best regards,
>
> Shaofeng Shi 史少锋