localtimestamp和current_timestamp插入mysql时区错乱

[徐振华]

Dear all；


 建表
> CREATE TABLE sink (
>   id INT,
>   prod_nm STRING,
>  dtm timestamp,
>  primary key(id)  NOT ENFORCED --  '这个是官方给出'
> ) 
> WITH (
>     'connector' = 'jdbc',
>     'url' = 'jdbc:mysql://10.0.0.0:3306/rs_report?useUnicode=true&characterEncoding=UTF-8',
>     'table-name' = 'sink',
>     'driver' = 'com.mysql.jdbc.Driver',
>     'username' = 'dps',
>     'password' = 'dps'
> );

insert into sink
 select id,prod_nm,current_timestamp from product;



这样插入mysql 后dtm时区是乱的， 应该插入的是当前时间减8个小时的，变成了当前时间减21小时
 
insert into sink
 select id,prod_nm,localtimestamp from product;



这样插入mysql 后dtm时区也是是乱的， 应该插入的是当前时间，变成了当前时间减8小时.

[Leonard Xu]

Hi,

> 这样插入mysql 后dtm时区是乱的， 应该插入的是当前时间减8个小时的，变成了当前时间减21小时

变成当前时间减21小时这个感觉好奇怪，方便贴下完整的代码和数据吗？

Best
Leonard

[xuzh-2]

CREATE TABLE product2 (
  id INT,
  prod_nm STRING,
 primary key(id)  NOT ENFORCED --  '这个是官方给出'
)
WITH (
    'connector' = 'jdbc',
    'url' = 'jdbc:mysql://10.12.5.37:3306/rs_report?useUnicode=true&characterEncoding=UTF-8',
    'table-name' = 'sink',
    'driver' = 'com.mysql.jdbc.Driver',
    'username' = 'dps',
    'password' = 'dps1234'
);




CREATE TABLE `sink2` (
  `local_dtm` varchar(100) DEFAULT NULL,
  `curr_dtm` varchar(100) DEFAULT NULL,
  `local_dtm_no_zone` varchar(100) DEFAULT NULL,
  `curr_dtm_no_zone` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8


drop table if exists sk;
CREATE TABLE sk (
 local_dtm TIMESTAMP,
 curr_dtm TIMESTAMP,
 local_dtm_no_zone   TIMESTAMP WITHOUT TIME ZONE,
 curr_dtm_no_zone   TIMESTAMP WITHOUT TIME ZONE
)
WITH (
    'connector' = 'jdbc',
    'url' = 'jdbc:mysql://10.12.5.37:3306/rs_report?useUnicode=true&characterEncoding=UTF-8',
    'table-name' = 'sink2',
    'driver' = 'com.mysql.jdbc.Driver',
    'username' = 'dps',
    'password' = 'dps1234'
);


insert into sk values(localtimestamp,current_timestamp,localtimestamp ,current_timestamp);


插入时间是：2020-09-09 15:25:55.416
插入后结果：
local_dtm                           | curr_dtm                            | local_dtm_no_zone          | curr_dtm_no_zone           |
------------------------            | ------------------------            | ------------------------           | ------------------------            |
2020-09-09 02:25:55.416  | 2020-09-08 18:25:55.416 | 2020-09-09 02:25:55.416 | 2020-09-08 18:25:55.416 |





------------------ 原始邮件 ------------------
发件人: "Leonard Xu"<[hidden email]&gt;;
发送时间: 2020年9月9日(星期三) 晚上9:51
收件人: "user-zh"<[hidden email]&gt;;
主题: Re: localtimestamp和current_timestamp插入mysql时区错乱



Hi,

&gt;&nbsp;这样插入mysql&nbsp;后dtm时区是乱的，&nbsp;应该插入的是当前时间减8个小时的，变成了当前时间减21小时

变成当前时间减21小时这个感觉好奇怪，方便贴下完整的代码和数据吗？

Best
Leonard

[Leonard Xu]

Hi

> insert into sk values(localtimestamp,current_timestamp,localtimestamp,current_timestamp);


1. 你flink里声明的字段类型和数据库的类型不匹配，需要保持一致，数据库里是varchar，flink是timestamp，完整类型映射可以参考[1]
2. 你插入的两个字段（ocaltimestamp,current_timestamp）的值可以贴出来看看？

Best
Leonard
[1]https://ci.apache.org/projects/flink/flink-docs-master/zh/dev/table/connectors/jdbc.html#data-type-mapping <https://ci.apache.org/projects/flink/flink-docs-master/zh/dev/table/connectors/jdbc.html#data-type-mapping>

[徐振华]

1、timestamp也是测过的，结果是一样的，后面才改成varchar的
2、上面的例子后面就是贴测试结果
插入时间是：2020-09-09&nbsp;15:25:55.416
插入后结果：&nbsp;
local_dtm &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | curr_dtm &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| local_dtm_no_zone &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| curr_dtm_no_zone &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; |&nbsp;
------------------------ &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| ------------------------ &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| ------------------------ &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | ------------------------ &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;|&nbsp;
2020-09-09&nbsp;02:25:55.416 &nbsp;|&nbsp;2020-09-08&nbsp;18:25:55.416 |&nbsp;2020-09-09&nbsp;02:25:55.416 |&nbsp;2020-09-08&nbsp;18:25:55.416 |






------------------&nbsp;原始邮件&nbsp;------------------
发件人:                                                                                                                        "xuzh"                                                                                    <[hidden email]&gt;;
发送时间:&nbsp;2020年9月9日(星期三) 下午3:06
收件人:&nbsp;"user-zh"<[hidden email]&gt;;

主题:&nbsp;localtimestamp和current_timestamp插入mysql时区错乱



Dear all；


&nbsp;建表
&gt; CREATE TABLE sink (
&gt;&nbsp; &nbsp;id INT,
&gt;&nbsp; &nbsp;prod_nm STRING,
&gt;&nbsp; dtm timestamp,
&gt;&nbsp; primary key(id)&nbsp; NOT ENFORCED --&nbsp; '这个是官方给出'
&gt; )
&gt; WITH (
&gt;&nbsp; &nbsp; &nbsp;'connector' = 'jdbc',
&gt;&nbsp; &nbsp; &nbsp;'url' = 'jdbc:mysql://10.0.0.0:3306/rs_report?useUnicode=true&amp;characterEncoding=UTF-8',
&gt;&nbsp; &nbsp; &nbsp;'table-name' = 'sink',
&gt;&nbsp; &nbsp; &nbsp;'driver' = 'com.mysql.jdbc.Driver',
&gt;&nbsp; &nbsp; &nbsp;'username' = 'dps',
&gt;&nbsp; &nbsp; &nbsp;'password' = 'dps'
&gt; );

insert into sink
&nbsp;select id,prod_nm,current_timestamp from product;



这样插入mysql 后dtm时区是乱的， 应该插入的是当前时间减8个小时的，变成了当前时间减21小时
&nbsp;
insert into sink
&nbsp;select id,prod_nm,localtimestamp from product;



这样插入mysql 后dtm时区也是是乱的， 应该插入的是当前时间，变成了当前时间减8小时.