event time timezone is not correct

[sen]

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"]}]}

[Piotr Nowojski-3]

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@...%3E

I think it answers the same question and links to tickets for Timezone support.

Piotrek

On 4 Mar 2019, at 08:55, 孙森 <[hidden email]> 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"]}]}

[sen]

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 <[hidden email]> 写道：

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@...%3E

I think it answers the same question and links to tickets for Timezone support.

Piotrek

On 4 Mar 2019, at 08:55, 孙森 <[hidden email]> 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"]}]}

[Piotr Nowojski-3]

Hi,

Yes, unfortunately this is still not resolved issue :(

Piotrek

On 5 Mar 2019, at 04:34, 孙森 <[hidden email]> 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 <[hidden email]> 写道：

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@...%3E

I think it answers the same question and links to tickets for Timezone support.

Piotrek

On 4 Mar 2019, at 08:55, 孙森 <[hidden email]> 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"]}]}