【时区】Flink JDBC 和CDC时间字段时区测试及时间基准

忘是亡心i 2024-02-28 06:00 124阅读 0赞

关联文章:  
[各种时间类型和timezone关系浅析][timezone]

### 一、测试目的和值 ###

#### 1. 测试一般的数据库不含time zone的类型的时区。 ####

*  mysql timestamp(3) 类型
 *  postgres timestamp(3) 类型
 *  sqlserver datetime2(3) 类型
 *  oracle类型 TIMESTAMP(3) 类型  
    在以下测试之中均为`ts`字段

#### 2.测试CDC中元数据`op_ts` 时区 ####

op_tsTIMESTAMP_LTZ(3) NOT NULL当前记录表在数据库中更新的时间。如果从表的快照而不是 binlog 读取记录，该值将始终为0。|

在以下测试中cdc表建表均使用`ts_ms TIMESTAMP_LTZ(3) METADATA FROM 'op_ts' VIRTUAL` 表示。  
cdc在读取表时候分两个阶段：

1.  全量读取阶段，特点是jdbc读取，读取数据中`op=r`
2.  增量读取阶段，特点是log读取，读取数据中`op=c或u或d`  
    op在截图中看到如`3="r"` 或者 `3="r"`，3是op字段的索引值。  
    `ts_ms`在全量阶段读取数据以下成为`READ数据`  
    `ts_ms`在增量阶段读取数据以下成为`CREATE数据`

#### 3. flink 数据时间表示和时区 ####

flink Table中时间必须使用`org.apache.flink.table.data.TimestampData`对象表示。

@PublicEvolving  
    public final class TimestampData implements Comparable<TimestampData> {
          
        private final long millisecond;  
        private final int nanoOfMillisecond;
    }

此类型使用如下两个值联合表示记录时间。并不记录时区数据。

实战测试：

@Test  
    public void testTimeZone(){
          
      
        // 常识：Epoch就是值utc的0时间点，是全局绝对时间点，本质是`ZoneOffset.of("+0")`下的0时间。与`January 1, 1970, 00:00:00 GMT`视为等同。  
        // GMT是前世界标准时，UTC是现世界标准时。UTC 比 GMT更精准，以原子时计时，适应现代社会的精确计时。  
        // 28800000=8*3600*1000。8小时毫秒值。  
      
        // 如下时间是+8时区的数据库存储的不带时区的时间：2023-09-28T09:43:20.320  
        long ts=1695894200320L;  
      
        // 如果将ts当做utc时间0时刻转为字符串则会导致时间+8 hour。2023-09-28 17:43:20。这是一般常用的在线转换时间的结果。因其默认是是epoch时间，所以转换后会+8h。  
        // 可见数据库读取的不带timezone时间的毫秒值，并不是以utc0时间(epoch)为基准的，而是以当前时区0为基准的。  
      
        // LocalDateTime对象本质支持LocalDate和LocalTime两个对象，LocalDate持有Integer的`年`,`月`,`日`。LocalTime则持有Integer的`时`,`分`,`秒`等和java.util.Date类型并不一样。  
        // LocalDateTime 的带有ZoneOffset方法比较难理解，此处：  
        // epochSecond 当然值的是epoch的秒数，是绝对时间概念和`java.util.Date.getTime()/1000`对应的，而offset是指此epoch秒数需要偏移的时间量。  
        // 内部代码是`long localSecond = epochSecond + offset.getTotalSeconds();`。  
      
        // 如下代码是正确的，因为java中的`java.util.Date`类和`java.sql.Timestamp`类型都是持有绝对时间的类，`Date.getTime`获得也是相对于Epoch的毫秒值（Returns the number of milliseconds since January 1, 1970, 00:00:00 GMT）。  
        LocalDateTime ldtFromDate = LocalDateTime.ofEpochSecond(new Date().getTime() / 1000, 0, ZoneOffset.of("+8"));  
        System.out.println(ldtFromDate);  // 2023-09-28T16:16:45。此时时钟也是16:17:44。  
        Date date0 = new Date(0); // number of milliseconds since the standard base time known as "the epoch"  
        System.out.println(date0.getTime()); // 0， date0.getTime()方法返回绝对时间Returns the number of milliseconds since January 1, 1970, 00:00:00 GMT  
      
        // 如下的提供`ZoneOffset.UTC`可以理解是告诉LocalDateTime我提供的epochSecond已是`localSecond=当地时间-当地时间的0点`不需要再做转换了。  
        LocalDateTime ldt0 = LocalDateTime.ofEpochSecond(0L, 0, ZoneOffset.UTC);  
        System.out.println(ldt0); // 1970-01-01T00:00  
        LocalDateTime ldt8 = LocalDateTime.ofEpochSecond(0L, 0, ZoneOffset.of("+8"));  
        System.out.println(ldt8); // 1970-01-01T08:00  
      
        // TimestampData 默认不会进行任何时区转换。也不存储任何时区信息。内部仅靠`long millisecond`和`int nanoOfMillisecond`存储信息，以便于序列化。  
        // millisecond 一般可以认为是本地时间。因其在toString方法中会不会进行时区转换，toString方法仅是调用了`toLocalDateTime()`，中进行简单运算，并最终调用`LocalDateTime.toString`方法。  
        TimestampData td0 = TimestampData.fromEpochMillis(0); // 相当于LocalDateTime.ofEpochSecond(0, 0, ZoneOffset.UTC)。  
        System.out.println(td0); // 1970-01-01T00:00。可见TimestampData输出转字符串的时间就是以utc时间为基准的这和java.util.Date类型是一致的。  
      
        LocalDateTime ldt = LocalDateTime.ofEpochSecond(  
                ts / 1000  
                , (int) (ts % 1000 * 1_000_000)  
                , ZoneOffset.UTC);  
        System.out.println(ldt); // 2023-09-28T09:43:20.320  
        TimestampData td = TimestampData.fromEpochMillis(ts);  
        System.out.println(td); // 2023-09-28T09:43:20.320  
      
        Date date = new Date(ts); // 注意：参数date(the specified number of milliseconds since the standard base time known as "the epoch")应该是epoch但此时ts并不是epoch基准的而是本地local基准的。  
        System.out.println(date); // Thu Sep 28 17:43:20 CST 2023,CST就是北京时间了，其在toString方法中`BaseCalendar.Date date = normalize();`进行了时区转换即+8了。  
    }

#### 4. 测试组件版本 ####

*  flink 1.13
 *  flink-cdc 2.2.1
 *  flink-connector-jdbc 自己定制的，根据`3.1.1-1.17`版本修改而来。

### 二、本测试共测试四大数据库： ###

*  mysql
 *  postgres
 *  sqlserver
 *  oracle

二、每种数据库测试8项：

*  database-SQL  
    直接从数据中读取数据，是测试的基准值
 *  cdc-RowData  
    使用cdc的SQL API从数据库中读取值并在 `com.ververica.cdc.debezium.table.AppendMetadataCollector#collect` 方法中debug得到数据
 *  cdc-SQL(测试除ts\_ms的字段)  
    使用cdc的SQL API读取值使用flink sql-client查询，用于测试除ts\_ms的字段。因ts\_ms准确性需分两种情况讨论。
 *  cdc-SQL-RealTime(测试ts\_ms)  
    使用cdc的SQL API从读取值，左上角是系统时间，下侧是实时读取的数据。
 *  cdc-Read数据(测试snapshot读取ts\_ms字段)  
    测试snapshot读取ts\_ms字段，即全量读取阶段的ts\_ms值，按照flink-cdc官方解释此四个数据的全量阶段值均为0(`1970-01-01 00:00:00`)。非0即为不正确。
 *  cdc-Create数据(测试incremental读取ts\_ms字段)  
    测试incremental读取ts\_ms字段，即增量读取阶段的ts\_ms值。按照flink-cdc官方解释此四个数据的增量阶段值为数据日志记录时间。
 *  jdbc-RowData  
    使用flink SQL API 读取connector是`jdbc`的表数据`org.apache.flink.connector.jdbc.table.JdbcRowDataInputFormat#nextRecord`的方法中debug得到数据。。不含tm\_ms数据。
 *  jdbc-SQL  
    使用flink SQL API 读取connector是`jdbc`的表数据。使用flink sql-client查询。。不含tm\_ms数据。

### 三、测试过程数据 ###

#### 3.1 mysql ####

##### 3.1.1 database-SQL #####

![在这里插入图片描述][88f6f86d05d84ebda4e46873f1b80df7.png]

##### 3.1.2 cdc-RowData #####

![在这里插入图片描述][118699995d554512a77611ad8eba3468.png]

##### 3.1.3 cdc-SQL(测试除ts\_ms的字段) #####

![!\[\[image-20230927163847043.png|201\]\]][image-20230927163847043.png_201]

##### 3.1.4 cdc-SQL-RealTime(测试ts\_ms) #####

如下：上侧（win系统显示时间截图），下侧（cdc-query的ts\_ms）  
如果基本一致（不是差值8h），说明cdc-query的ts\_ms是正确的的。  
![!\[\[image-20230928132434484.png|325\]\]][image-20230928132434484.png_325]

##### 3.1.5 cdc-Read数据(测试snapshot读取ts\_ms字段) #####

![!\[\[image-20230928100333641.png\]\]][image-20230928100333641.png]

##### 3.1.6 cdc-Create数据(测试incremental读取ts\_ms字段) #####

![!\[\[image-20230928101529479.png\]\]][image-20230928101529479.png]

##### 3.1.7 jdbc-RowData #####

![!\[\[image-20230927172538194.png\]\]][image-20230927172538194.png]

##### 3.1.8 jdbc-SQL #####

![!\[\[image-20230927171613530.png|206\]\]][image-20230927171613530.png_206]

#### 3.2 postgres ####

##### 3.2.1 database-SQL #####

![!\[\[image-20230927145744323.png\]\]][image-20230927145744323.png]

##### 3.2.2 cdc #####

cdc-RowData  
![!\[\[image-20230927145825569.png\]\]][image-20230927145825569.png]

##### 3.2.3 cdc-SQL(测试除ts\_ms的字段) #####

![!\[\[image-20230927151801248.png|200\]\]][image-20230927151801248.png_200]

##### 3.2.4 cdc-SQL-RealTime(测试ts\_ms) #####

![!\[\[image-20230928132850256.png|325\]\]][image-20230928132850256.png_325]

##### 3.2.5 cdc-Read数据(测试snapshot读取ts\_ms字段) #####

![!\[\[image-20230928095911025.png\]\]][image-20230928095911025.png]

##### 3.2.6 cdc-Create数据(测试incremental读取ts\_ms字段) #####

![!\[\[image-20230928101453266.png\]\]][image-20230928101453266.png]

##### 3.2.7 jdbc #####

jdbc-RowData  
![!\[\[image-20230927173637049.png\]\]][image-20230927173637049.png]

##### 3.2.8 jdbc-SQL #####

![!\[\[image-20230927173456643.png|212\]\]][image-20230927173456643.png_212]

#### 3.3 sqlserver ####

##### 3.3.1 database-SQL #####

![!\[\[image-20230927163637993.png\]\]][image-20230927163637993.png]

##### 3.3.2 cdc-RowData #####

![!\[\[image-20230927163611807.png\]\]][image-20230927163611807.png]

##### 3.3.3 cdc-SQL(测试除ts\_ms的字段) #####

![!\[\[image-20230927163808365.png|192\]\]][image-20230927163808365.png_192]

##### 3.3.4 cdc-SQL-RealTime(测试ts\_ms) #####

![!\[\[image-20230928133349412.png|350\]\]][image-20230928133349412.png_350]

##### 3.3.5 cdc-Read数据(测试snapshot读取ts\_ms字段) #####

![!\[\[image-20230928094006306.png\]\]][image-20230928094006306.png]

##### 3.3.6 cdc-Create数据(测试incremental读取ts\_ms字段) #####

![!\[\[image-20230928101415704.png\]\]][image-20230928101415704.png]

##### 3.3.7 jdbc-RowData #####

![!\[\[image-20230927174904854.png\]\]][image-20230927174904854.png]

##### 3.3.8 jdbc-SQL #####

![!\[\[image-20230927182456589.png|194\]\]][image-20230927182456589.png_194]

#### 3.4 oracle ####

##### 3.4.1 database-SQL #####

![!\[\[image-20230927160526864.png\]\]][image-20230927160526864.png]

##### 3.4.2 cdc-RowData #####

![!\[\[image-20230927160425443.png\]\]][image-20230927160425443.png]

##### 3.4.3 cdc-SQL(测试除ts\_ms的字段) #####

![!\[\[image-20230927160753056.png|191\]\]][image-20230927160753056.png_191]

##### 3.4.3 cdc-SQL-RealTime(测试ts\_ms) #####

![!\[\[image-20230928133736851.png|400\]\]][image-20230928133736851.png_400]

##### 3.4.4 cdc-Read数据(测试snapshot读取ts\_ms字段) #####

![!\[\[image-20230928101223538.png\]\]][image-20230928101223538.png]

##### 3.4.5 cdc-Create数据(测试incremental读取ts\_ms字段) #####

![!\[\[image-20230928101030948.png\]\]][image-20230928101030948.png]

##### 3.4.7 jdbc-RowData #####

![!\[\[image-20230927183056565.png\]\]][image-20230927183056565.png]

##### 3.4.8 jdbc-SQL #####

![!\[\[image-20230927182935788.png|203\]\]][image-20230927182935788.png_203]

### 四、结论 ###

（1）数据库获取的`without time zone`在flink中都是以本地时间的存储的。可以使用`LocalDateTime.ofEpochSecond(long epochSecond, int nanoOfSecond, ZoneOffset.UTC)`直接获取。  
（2）Flink中的TimestampData中存储的一般可以认为是本地时间。但需要注意：TimestampData 不可将 `instant 相关方法`和 `localDateTime 、Timestamp 相关方法`混用。因为instant代表与epoch时间差。而后两者代表与local是时间差。  
（3）Flink程序中时间的标准值都是local本地的。因其在Sql API（sql-client）中打印出的结果会与原始数据库中打印的一致。

如下图中红色字体的是错误的数据，使用CDC需要额外注意并进行转换。  
![!\[\[image-20230928164847790.png\]\]][image-20230928164847790.png]

### 五、附录 ###

#### 5.1 查询数据库时区SQL ####

-- mysql 以：time_zone 为准，system_time_zone至服务器时区
    show variables like '%time_zone%';
    
    -- postgres
    show time zone;
    
    -- sqlserver
    DECLARE
    @TimeZone NVARCHAR(255)
    EXEC
    master.dbo.xp_instance_regread
    N'HKEY_LOCAL_MACHINE'
    ,
    N'SYSTEM\CurrentControlSet\Control\TimeZoneInformation'
    ,
    N'TimeZoneKeyName'
    ,
    @TimeZone
    OUTPUT
    SELECT
    @TimeZone 
    
    -- oracle
    select dbtimezone from dual;

[timezone]: https://blog.csdn.net/lisacumt/article/details/131826774
[88f6f86d05d84ebda4e46873f1b80df7.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/28/06b4f07349da4f0da0e60529649d28e5.png
[118699995d554512a77611ad8eba3468.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/28/4a7e736e7c644c22ac18a9d9299a339a.png
[image-20230927163847043.png_201]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/28/7c835f09fc79493ebce5bbded3939ab6.png
[image-20230928132434484.png_325]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/28/f059b377a410425b994c65ebcd8c02b8.png
[image-20230928100333641.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/28/eaf95bf51b2d41f2a7f9e085b7bb40ba.png
[image-20230928101529479.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/28/812664be8b0f44f69ecb095745e27be6.png
[image-20230927172538194.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/28/55b5115e706e495ea14294c26fa1f5da.png
[image-20230927171613530.png_206]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/28/7d804e5a6f054332ad60655cb52da95c.png
[image-20230927145744323.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/28/a774b6a84b3844b0a3e7d15e56961bba.png
[image-20230927145825569.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/28/4e10235662a44cb6b8274bfa7b64f97d.png
[image-20230927151801248.png_200]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/28/04ce7f13daa148ee8c1f278d5371c869.png
[image-20230928132850256.png_325]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/28/1103eb0fff2340738b5843d6afef0f35.png
[image-20230928095911025.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/28/b281b45f9da64fc689be38bdb5e52ff5.png
[image-20230928101453266.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/28/06f4391d94e742ee959747b80d7dc7f2.png
[image-20230927173637049.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/28/0e100c354d844e07b246863a647c86a7.png
[image-20230927173456643.png_212]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/28/026d2fa5eb3d4787b7b84c24f5c4809f.png
[image-20230927163637993.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/28/a4db93d9037c4941b2f2811f72964ebd.png
[image-20230927163611807.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/28/74399ec74e184e4b9dd91b39791d65e6.png
[image-20230927163808365.png_192]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/28/f3171fc411ce4d0789167453eeba767f.png
[image-20230928133349412.png_350]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/28/0a1a18106ca34e62956d7ba821055f50.png
[image-20230928094006306.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/28/5c816156816146b7baffad7838449ddd.png
[image-20230928101415704.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/28/5be219d5f9e440b3905d6ef9953e2c11.png
[image-20230927174904854.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/28/5df6908b77744035b2e6096c0ac0ddf9.png
[image-20230927182456589.png_194]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/28/4d7772adecf14983bb8632eadc1c2342.png
[image-20230927160526864.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/28/c41d4e6d31ee48c29c9ac14c5bc6468e.png
[image-20230927160425443.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/28/23b5600b60d343ec8a4bbb40513692eb.png
[image-20230927160753056.png_191]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/28/f9381dd5ed32473cb4305f5a3c4b8582.png
[image-20230928133736851.png_400]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/28/061a6a487cac4e618a029dda0a6d3c4b.png
[image-20230928101223538.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/28/3fd0c8e4d8ee483d8a143c7a41cb3ecd.png
[image-20230928101030948.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/28/475ac59290b84bc997a7a836d79306ff.png
[image-20230927183056565.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/28/5079c08a4e804029818cbf40f6b1798f.png
[image-20230927182935788.png_203]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/28/b4669696b5c44cdcb854be17e2ece202.png
[image-20230928164847790.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/28/c1f06b12673444bdabe7d45988127af5.png