You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@flink.apache.org by 孙森 <se...@163.com> on 2019/03/04 07:55:22 UTC
event time timezone is not correct
Hi all:
I am using flink sql with event time, but the field which acts as the routine is not correct in the output. There’s an eight-hour time difference.
Any suggestion?
My input is (ums_ts_ acts as the rowtime):
{"schema":{"namespace":"en2.*.*.*","fields":[{"name":"ums_id_","type":"long","nullable":false},{"name":"ums_ts_","type":"datetime","nullable":false},{"name":"ums_op_","type":"string","nullable":false},{"name":"key","type":"int","nullable":false},{"name":"value1","type":"string","nullable":true},{"name":"value2","type":"long","nullable":false}]},"payload":[{"tuple":["1","2018-04-11 12:40:01.345123","i","10","aa1","10"]},{"tuple":["22","2018-04-11 12:40:20.345123","u","10","aa2","11"]},{"tuple":["311","2018-04-11 12:40:39.345123","d","10","aa3","12"]}]}
My sql is:
select key, COUNT(*) AS count_sen, SUM(value2) AS ages,TUMBLE_START(ums_ts_, INTERVAL '1' SECOND) as window_start FROM sen2 GROUP BY TUMBLE(ums_ts_, INTERVAL '1' SECOND), key;
The output is :
{"schema":{"namespace":"en3.*.*.*","fields":[{"name":"key","type":"int","nullable":true},{"name":"count_sen","type":"long","nullable":true},{"name":"ages","type":"long","nullable":true},{"name":"window_start","type":"datetime","nullable":true}]},"payload":[{"tuple":["10","1","10","2018-04-11 04:40:01.0"]}]}
{"schema":{"namespace":"en3.*.*.*","fields":[{"name":"key","type":"int","nullable":true},{"name":"count_sen","type":"long","nullable":true},{"name":"ages","type":"long","nullable":true},{"name":"window_start","type":"datetime","nullable":true}]},"payload":[{"tuple":["10","1","11","2018-04-11 04:40:20.0"]}]}
Re: event time timezone is not correct
Posted by Piotr Nowojski <pi...@ververica.com>.
Hi,
Yes, unfortunately this is still not resolved issue :(
Piotrek
> On 5 Mar 2019, at 04:34, 孙森 <se...@163.com> wrote:
>
> Thanks Piotrek.
>
> It seems the question has not been solved. I will try to use the TIMESTAMPADD(timeUnit, integer, datetime) instead .
>
> Best
> Sen
>
>> 在 2019年3月4日，下午11:29，Piotr Nowojski <piotr@ververica.com <ma...@ververica.com>> 写道：
>>
>> Hi,
>>
>> I think that Flink SQL works currently only in UTC, so the 8 hours difference is a result of you using GMT+8 time stamps somewhere. Please take a look at this thread:
>>
>> http://mail-archives.apache.org/mod_mbox/flink-user/201711.mbox/%3C2e1eb190-26a0-b288-39a4-683b463f45ac@apache.org%3E <http://mail-archives.apache.org/mod_mbox/flink-user/201711.mbox/%3C2e1eb190-26a0-b288-39a4-683b463f45ac@apache.org%3E>
>>
>> I think it answers the same question and links to tickets for Timezone support.
>>
>> Piotrek
>>
>>> On 4 Mar 2019, at 08:55, 孙森 <sennybob@163.com <ma...@163.com>> wrote:
>>>
>>> Hi all:
>>> I am using flink sql with event time, but the field which acts as the routine is not correct in the output. There’s an eight-hour time difference.
>>> Any suggestion?
>>>
>>>
>>> My input is (ums_ts_ acts as the rowtime):
>>>
>>> {"schema":{"namespace":"en2.*.*.*","fields":[{"name":"ums_id_","type":"long","nullable":false},{"name":"ums_ts_","type":"datetime","nullable":false},{"name":"ums_op_","type":"string","nullable":false},{"name":"key","type":"int","nullable":false},{"name":"value1","type":"string","nullable":true},{"name":"value2","type":"long","nullable":false}]},"payload":[{"tuple":["1","2018-04-11 12:40:01.345123","i","10","aa1","10"]},{"tuple":["22","2018-04-11 12:40:20.345123","u","10","aa2","11"]},{"tuple":["311","2018-04-11 12:40:39.345123","d","10","aa3","12"]}]}
>>>
>>>
>>> My sql is:
>>>
>>> select key, COUNT(*) AS count_sen, SUM(value2) AS ages,TUMBLE_START(ums_ts_, INTERVAL '1' SECOND) as window_start FROM sen2 GROUP BY TUMBLE(ums_ts_, INTERVAL '1' SECOND), key;
>>>
>>>
>>> The output is :
>>>
>>> {"schema":{"namespace":"en3.*.*.*","fields":[{"name":"key","type":"int","nullable":true},{"name":"count_sen","type":"long","nullable":true},{"name":"ages","type":"long","nullable":true},{"name":"window_start","type":"datetime","nullable":true}]},"payload":[{"tuple":["10","1","10","2018-04-11 04:40:01.0"]}]}
>>> {"schema":{"namespace":"en3.*.*.*","fields":[{"name":"key","type":"int","nullable":true},{"name":"count_sen","type":"long","nullable":true},{"name":"ages","type":"long","nullable":true},{"name":"window_start","type":"datetime","nullable":true}]},"payload":[{"tuple":["10","1","11","2018-04-11 04:40:20.0"]}]}
>>>
>>>
>>>
>>
>
Re: event time timezone is not correct
Posted by 孙森 <se...@163.com>.
Thanks Piotrek.
It seems the question has not been solved. I will try to use the TIMESTAMPADD(timeUnit, integer, datetime) instead .
Best
Sen
> 在 2019年3月4日，下午11:29，Piotr Nowojski <pi...@ververica.com> 写道：
>
> Hi,
>
> I think that Flink SQL works currently only in UTC, so the 8 hours difference is a result of you using GMT+8 time stamps somewhere. Please take a look at this thread:
>
> http://mail-archives.apache.org/mod_mbox/flink-user/201711.mbox/%3C2e1eb190-26a0-b288-39a4-683b463f45ac@apache.org%3E <http://mail-archives.apache.org/mod_mbox/flink-user/201711.mbox/%3C2e1eb190-26a0-b288-39a4-683b463f45ac@apache.org%3E>
>
> I think it answers the same question and links to tickets for Timezone support.
>
> Piotrek
>
>> On 4 Mar 2019, at 08:55, 孙森 <sennybob@163.com <ma...@163.com>> wrote:
>>
>> Hi all:
>> I am using flink sql with event time, but the field which acts as the routine is not correct in the output. There’s an eight-hour time difference.
>> Any suggestion?
>>
>>
>> My input is (ums_ts_ acts as the rowtime):
>>
>> {"schema":{"namespace":"en2.*.*.*","fields":[{"name":"ums_id_","type":"long","nullable":false},{"name":"ums_ts_","type":"datetime","nullable":false},{"name":"ums_op_","type":"string","nullable":false},{"name":"key","type":"int","nullable":false},{"name":"value1","type":"string","nullable":true},{"name":"value2","type":"long","nullable":false}]},"payload":[{"tuple":["1","2018-04-11 12:40:01.345123","i","10","aa1","10"]},{"tuple":["22","2018-04-11 12:40:20.345123","u","10","aa2","11"]},{"tuple":["311","2018-04-11 12:40:39.345123","d","10","aa3","12"]}]}
>>
>>
>> My sql is:
>>
>> select key, COUNT(*) AS count_sen, SUM(value2) AS ages,TUMBLE_START(ums_ts_, INTERVAL '1' SECOND) as window_start FROM sen2 GROUP BY TUMBLE(ums_ts_, INTERVAL '1' SECOND), key;
>>
>>
>> The output is :
>>
>> {"schema":{"namespace":"en3.*.*.*","fields":[{"name":"key","type":"int","nullable":true},{"name":"count_sen","type":"long","nullable":true},{"name":"ages","type":"long","nullable":true},{"name":"window_start","type":"datetime","nullable":true}]},"payload":[{"tuple":["10","1","10","2018-04-11 04:40:01.0"]}]}
>> {"schema":{"namespace":"en3.*.*.*","fields":[{"name":"key","type":"int","nullable":true},{"name":"count_sen","type":"long","nullable":true},{"name":"ages","type":"long","nullable":true},{"name":"window_start","type":"datetime","nullable":true}]},"payload":[{"tuple":["10","1","11","2018-04-11 04:40:20.0"]}]}
>>
>>
>>
>
Re: event time timezone is not correct
Posted by Piotr Nowojski <pi...@ververica.com>.
Hi,
I think that Flink SQL works currently only in UTC, so the 8 hours difference is a result of you using GMT+8 time stamps somewhere. Please take a look at this thread:
http://mail-archives.apache.org/mod_mbox/flink-user/201711.mbox/%3C2e1eb190-26a0-b288-39a4-683b463f45ac@apache.org%3E
I think it answers the same question and links to tickets for Timezone support.
Piotrek
> On 4 Mar 2019, at 08:55, 孙森 <se...@163.com> wrote:
>
> Hi all:
> I am using flink sql with event time, but the field which acts as the routine is not correct in the output. There’s an eight-hour time difference.
> Any suggestion?
>
>
> My input is (ums_ts_ acts as the rowtime):
>
> {"schema":{"namespace":"en2.*.*.*","fields":[{"name":"ums_id_","type":"long","nullable":false},{"name":"ums_ts_","type":"datetime","nullable":false},{"name":"ums_op_","type":"string","nullable":false},{"name":"key","type":"int","nullable":false},{"name":"value1","type":"string","nullable":true},{"name":"value2","type":"long","nullable":false}]},"payload":[{"tuple":["1","2018-04-11 12:40:01.345123","i","10","aa1","10"]},{"tuple":["22","2018-04-11 12:40:20.345123","u","10","aa2","11"]},{"tuple":["311","2018-04-11 12:40:39.345123","d","10","aa3","12"]}]}
>
>
> My sql is:
>
> select key, COUNT(*) AS count_sen, SUM(value2) AS ages,TUMBLE_START(ums_ts_, INTERVAL '1' SECOND) as window_start FROM sen2 GROUP BY TUMBLE(ums_ts_, INTERVAL '1' SECOND), key;
>
>
> The output is :
>
> {"schema":{"namespace":"en3.*.*.*","fields":[{"name":"key","type":"int","nullable":true},{"name":"count_sen","type":"long","nullable":true},{"name":"ages","type":"long","nullable":true},{"name":"window_start","type":"datetime","nullable":true}]},"payload":[{"tuple":["10","1","10","2018-04-11 04:40:01.0"]}]}
> {"schema":{"namespace":"en3.*.*.*","fields":[{"name":"key","type":"int","nullable":true},{"name":"count_sen","type":"long","nullable":true},{"name":"ages","type":"long","nullable":true},{"name":"window_start","type":"datetime","nullable":true}]},"payload":[{"tuple":["10","1","11","2018-04-11 04:40:20.0"]}]}
>
>
>