关于debezium同步mysql字段类型的datetime、date、time、timestamp的格式转换说明

标签：12 String timestamp time datetime 2023 mysql date debezium

1.情景展示

使用debezium的插件：debezium-connector-oracle（io.debezium.connector.mysql.MySqlConnector），自动读取mysql日志binlog相关表的数据变更记录，然后将其发布到kafka topic当中。

现在遇到的问题是：

在mysql当中，表示日期类型的数据类型有：datetime、date和timestamp；

表示时间类型的是：time。

当使用io.debezium.connector.mysql.MySqlConnector插件读取mysql数据后，debezium自动对日期类型进行了格式转换。

2.具体分析

先说都转成了什么数据类型，再说怎么解决。

如上图所示，该表当中每个日期类型都有，下面我们进行实战演练。

抓取数据

我使用的REST API的方式来抓取mysql数据。

消费数据

得到其中一条数据如下：

所使用的debezium-connector-oracle插件版本号：2.2.1

id=4的mysql数据展示如下：

由此可以得出以下结论：

mysql datetime类型：使用的转换类是io.debezium.time.Timestamp，最终转成了时间戳（距离1970年01月01日0时0分0秒的毫秒数），形如：1702027934000。

mysql date类型：使用的转换类是io.debezium.time.Date，最终转成了天数（距离1970年01月01日的天数），形如：19699。

mysql timestamp类型：使用的转换类是io.debezium.time.ZonedTimestamp，最终转成了带时钟的时间戳，形如：2023-12-08T15:32:19Z

mysql time类型：使用的转换类是io.debezium.time.MicroTime，最终转成了微妙数（将小时转成了微妙），形如：34341000000。

3.解决方案

在mysql当中，最常用的是datetime类型，当将我们将时间戳转回日期字符串时，发现：所得时间比原来的时间早了8个小时。

这是因为：debezium在将datetime类型转成时间戳时，所用时区为：UTC，而我们在解析时间的时候，用的时区却是：UTC+8，所以最终转换得来的时间会比实际时间早8个小时。

网上搜到的解决办法都是：

无论是设置连接数据库所用时区还是设置数据库服务时间为东八区，统统不管用！

debezium在进行日期格式转换时，默认使用的是UTC，所以你再怎么设置数据库时间都无济于事！

第一种方案：更改debezium源码

源码地址：https://github.com/debezium/debezium

更改源码后，重新打包。

第二种方案：自定义开发Sink Connector

既然我们没有办法通过参数设置，将日期还原成正确时区，那我们完全可以从kafka拿数据自行解析。

先说如何将日期还原成真实日期。

datetime还原

// 年月日（大写M：表示月份，小写m：表示分钟）
public static final String FORMAT_DATE = "yyyy-MM-dd";
// 时分秒（大写H：表示24小时制，小写h：表示12小时制）
public static final String FORMAT_TIME = "HH:mm:ss";
// 年月日时分秒
public static final String FORMAT_DATE_TIME = FORMAT_DATE + " " + FORMAT_TIME;

public static String timestampToString(Long timestamp, String timeZone) {
    if (String.valueOf(timestamp).length() == 16) {// 16：毫秒
        return toDateTimeString(fromTimeMills(timestamp / 1000, timeZone), FORMAT_DATE_TIME);
    } else {// 13：秒
        return toDateTimeString(fromTimeMills(timestamp, timeZone), FORMAT_DATE_TIME);
    }
}

public static LocalDateTime fromTimeMills(long timeMills, String timeZone){
    return LocalDateTime.ofInstant(Instant.ofEpochMilli(timeMills), ZoneId.of(timeZone));
}

/**
 * 日期转字符串（日期+时间）
 * @attention: jdk>=1.8
 * @date: 2020年08月31日 0031 17:04
 * @param: dateTime
 * @param: pattern
 * @return: java.lang.String
 */
public static String toDateTimeString(LocalDateTime dateTime, String pattern) {
    DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern(pattern);
    return dateTimeFormatter.format(dateTime);
}

调用

timestampToString(1702027934000L,"UTC");// 2023-12-08 09:32:14

date还原

/**
 * 根据天数倒推日期
 * https://www.cnblogs.com/Marydon20170307/p/10672030.html
 * @param days
 * @return
 */
public static String getSomeDay(int days){
    SimpleDateFormat sdf = new SimpleDateFormat(FORMAT_DATE);
    Date date;
    try {
        date = sdf.parse("1970-01-01");
    } catch (ParseException e) {
        throw new RuntimeException(e);
    }
    Calendar calendar = Calendar.getInstance();
    calendar.setTime(date);
    calendar.add(Calendar.DAY_OF_YEAR, days);
    date = calendar.getTime();
    return sdf.format(date);
}

调用

getSomeDay(19699);// 2023-12-08

timestamp还原

/**
 * ISO 8601标准日期转成字符串
 * @param dateTimeStr
 * 2023-12-07T16:00:00Z
 * "2023-12-07T16:00:00Z"是一种ISO 8601标准的日期时间表示方式
 * 这个字符串表示的是一个特定的时间点：2023年12月7日，下午4点（16点），0分钟，0秒。其中“T”是时间标识符，“Z”表示的是协调世界时（UTC）。
 * 这种格式是可以精确到秒的时间戳
 * @return
 */
public static String fromISO8601(String dateTimeStr) {
    return toDateTimeString(toLocalDateTime(dateTimeStr), FORMAT_DATE_TIME);
}

/**
 * 日期字符串按指定格式转LocalDateTime
 * @attention:
 * @date: 2021/7/28 15:05
 * @param: dateTimeStr 日期字符串
 * 2023-12-07T16:00:00Z
 * "2023-12-07T16:00:00Z"是一种ISO 8601标准的日期时间表示方式
 * 这个字符串表示的是一个特定的时间点：2023年12月7日，下午4点（16点），0分钟，0秒。其中“T”是时间标识符，“Z”表示的是协调世界时（UTC）。
 * 这种格式是可以精确到秒的时间戳
 * @return: java.time.LocalDateTime
 */
public static LocalDateTime toLocalDateTime(String dateTimeStr) {
    // UTC时间
    DateTimeFormatter formatter = DateTimeFormatter.ISO_DATE_TIME;
    ZonedDateTime zonedDateTime = ZonedDateTime.parse(dateTimeStr, formatter);
    // TODO
    return zonedDateTime.toLocalDateTime().plusHours(-6);
}

说明：将UTC时间转成本地时间后，需要向前或向后推几个小时，需要自己算一下。

这里的-6，不一定对，我也不知道debezium是怎么进行转换的，竟然比实际时间早了6个小时，而不是8个小时。

调用

fromISO8601("2023-12-08T15:32:19Z");// 2023-12-08 09:32:19

至于自定义开发Sink Connector组件，请看下一篇文章。

写在最后

　　哪位大佬如若发现文章存在纰漏之处或需要补充更多内容，欢迎留言！！！