Can the BinaryLogClient be configured to set the ServerTimeZone when connecting to the database?

[salmanjones]

When I use mysql-binlog-connector-java to listen, the Date in WriteRowsEventData always appears to be 8 hours ahead of the correct time, even though my MySQL server is set to the Asia/Shanghai time zone. It seems that the time zone is incorrect. eg： mysql create_time : 2023/12/09 11:00:00 WriteRowsEventData create_time: 2023/12/09 19:00:00 ....

In order to resolve this issue, I pulled the source code and made modifications in the following Three places:

1.EventDeserializer.java add the following code:

/**
     * @see Set timezone setting to calculate unix_timestamp
     */
    public void setServerTimeZone(String tzId) {
        // Check valiad timezone, invalid timezone would be UTC
        String tz = TimeZone.getTimeZone(tzId).getID();

        for (EventDataDeserializer eventDataDeserializer : eventDataDeserializers.values()) {
            if (eventDataDeserializer instanceof AbstractRowsEventDataDeserializer) {
                AbstractRowsEventDataDeserializer deserializer =
                    (AbstractRowsEventDataDeserializer) eventDataDeserializer;
                // calculate time diff from UTC
                deserializer.setServerTimezoneDiff(TimeZone.getTimeZone(tz).getRawOffset());
            }
        }
    }

2.AbstractRowsEventDataDeserializer.java add the following code:

    private static int serverTimezoneDiff;
    void setServerTimezoneDiff(int serverTimezoneDiff) {
        this.serverTimezoneDiff = serverTimezoneDiff;
    }

3.AbstractRowsEventDataDeserializer.java modify the 'from' and 'fallbackToGC' as the following code:

public static long from(int year, int month, int day, int hour, int minute, int second, int millis) {
            if (year < 1582 || (year == 1582 && (month < 10 || (month == 10 && day < 15)))) {
                return fallbackToGC(year, month, day, hour, minute, second, millis);
            }
            long timestamp = 0;
            int numberOfLeapYears = leapYears(1970, year);
            timestamp += 366L * 24 * 60 * 60 * numberOfLeapYears;
            timestamp += 365L * 24 * 60 * 60 * (year - 1970 - numberOfLeapYears);
            long daysUpToMonth = isLeapYear(year) ? LEAP_YEAR_DAYS_BY_MONTH[month - 1] : YEAR_DAYS_BY_MONTH[month - 1];
            timestamp += ((daysUpToMonth + day - 1) * 24 * 60 * 60) +
                (hour * 60 * 60) + (minute * 60) + (second);
            timestamp = timestamp * 1000 + millis - serverTimezoneDiff;
            return timestamp;
        }

private static long fallbackToGC(int year, int month, int dayOfMonth, int hourOfDay,
                                         int minute, int second, int millis) {
            Calendar c = Calendar.getInstance(TimeZone.getTimeZone("GMT"));
            c.set(Calendar.YEAR, year);
            c.set(Calendar.MONTH, month - 1);
            c.set(Calendar.DAY_OF_MONTH, dayOfMonth);
            c.set(Calendar.HOUR_OF_DAY, hourOfDay);
            c.set(Calendar.MINUTE, minute);
            c.set(Calendar.SECOND, second);
            c.set(Calendar.MILLISECOND, millis);
            return c.getTimeInMillis() - serverTimezoneDiff;
        }

With the aforementioned modifications, mysql-binlog-connector-java can now set the time zone and resolve the issue of incorrect dates in different time zones.

[gongwenqiang]

good idea！！！