You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-zh@flink.apache.org by xuzh <hu...@foxmail.com> on 2020/09/09 07:06:52 UTC
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小时.
回复：localtimestamp和current_timestamp插入mysql时区错乱
Posted by xuzh <hu...@foxmail.com>.
1、timestamp也是测过的，结果是一样的，后面才改成varchar的
2、上面的例子后面就是贴测试结果
插入时间是：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 |
------------------ 原始邮件 ------------------
发件人: "xuzh" <huazhenxu@foxmail.com>;
发送时间: 2020年9月9日(星期三) 下午3:06
收件人: "user-zh"<user-zh@flink.apache.org>;
主题: 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小时.
Re: localtimestamp和current_timestamp插入mysql时区错乱
Posted by Leonard Xu <xb...@gmail.com>.
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>
回复： localtimestamp和current_timestamp插入mysql时区错乱
Posted by xuzh <hu...@qq.com>.
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"<xbjtdcq@gmail.com>;
发送时间: 2020年9月9日(星期三) 晚上9:51
收件人: "user-zh"<user-zh@flink.apache.org>;
主题: Re: localtimestamp和current_timestamp插入mysql时区错乱
Hi,
> 这样插入mysql 后dtm时区是乱的， 应该插入的是当前时间减8个小时的，变成了当前时间减21小时
变成当前时间减21小时这个感觉好奇怪，方便贴下完整的代码和数据吗？
Best
Leonard
Re: localtimestamp和current_timestamp插入mysql时区错乱
Posted by Leonard Xu <xb...@gmail.com>.
Hi,
> 这样插入mysql 后dtm时区是乱的， 应该插入的是当前时间减8个小时的，变成了当前时间减21小时
变成当前时间减21小时这个感觉好奇怪，方便贴下完整的代码和数据吗？
Best
Leonard