How to set timezone for deserializeDatetime?

[gywndi]

Hi~

I make some little project with mysql-binlog-connector-java.

First all, I'm in Korea, and timezone is GMT+09:00.

mysql> show variables like '%time%zone';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | KST    |
| time_zone        | SYSTEM |
+------------------+--------+

mysql> select * from test;
+---+--------+---------------------+---------------------+
| i | d      | dt                  | ts                  |
+---+--------+---------------------+---------------------+
| 5 | abc    | 2017-12-20 18:01:24 | 2017-12-20 18:01:24 |
| 6 | abc    | 2017-12-20 18:01:24 | 2017-12-20 18:01:24 |
| 8 | abc    | 2017-12-20 18:58:45 | 2017-12-20 18:58:45 |
+---+--------+---------------------+---------------------+

I read the binlog event, but it shows wrong datetime information as below.

---------------------------------
dt : Thu Dec 21 01:01:24 KST 2017
ts : 2017-12-20 18:01:24.0
---------------------------------
dt : Thu Dec 21 01:01:24 KST 2017
ts : 2017-12-20 18:01:24.0
---------------------------------
dt : Thu Dec 21 01:58:45 KST 2017
ts : 2017-12-20 18:58:45.0

(1,'abc','2017-12-21 01:01:24','2017-12-20 18:01:24')
(2,'abc','2017-12-21 01:01:24','2017-12-20 18:01:24')
(3,'abc','2017-12-21 01:58:45','2017-12-20 18:58:45')

It seems that mysql loads into binlog on KST basis, while mysql-binlog-connector-java deserializes data on UTC basis. Therefore, +09:00 is eaten once more, and the wrong time seems to be recorded.

I think it is a simple solution to subtract 9 hours backwards ... but I do not think it is a proper solution. Is there any way to get data in KST instead of UTC according to user time zone setting?

Thanks.
Chan.

[gywndi]

I can not understand why the time zone attribute is missing in the deserializes part of datetime.

AbstractRowsEventDataDeserializer.java

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;
    return timestamp;
}

Same class (AbstractRowsEventDataDeserializer) on this method..

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"));
    ....
}

TimeZone.getTimeZone("GMT") must be removed.

[gywndi]

I modified like below, and test againg.. I've got a correct result.

public static long from(int year, int month, int day, int hour, int minute, int second, int millis) {
    return fallbackToGC(year, month, day, hour, minute, second, 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;
//    return timestamp;
}

private static long fallbackToGC(int year, int month, int dayOfMonth, int hourOfDay,
                                 int minute, int second, int millis) {
    Calendar c = Calendar.getInstance();
//  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();
}

This is result.

---------------------------------
dt : Thu Dec 20 18:01:24 KST 2017
ts : 2017-12-20 18:01:24.0
---------------------------------
dt : Thu Dec 20 18:01:24 KST 2017
ts : 2017-12-20 18:01:24.0
---------------------------------
dt : Thu Dec 20 18:58:45 KST 2017
ts : 2017-12-20 18:58:45.0

(1,'abc','2017-12-20 18:01:24','2017-12-20 18:01:24')
(2,'abc','2017-12-20 18:01:24','2017-12-20 18:01:24')
(3,'abc','2017-12-20 18:58:45','2017-12-20 18:58:45')

[gywndi]

Hi~

I have checked the implementation of datetime with the open replicator source. I can not know why you calculate and handle unix_timestamp based on fixed date.
I think, using the java date object right away don't have any risk.

public static java.util.Date toDatetime2(long value, int nanos) {
	final long x = (value >> 22) & 0x1FFFFL;
	final int year = (int)(x / 13);
	final int month = (int)(x % 13);
	final int day = ((int)(value >> 17)) & 0x1F;
	final int hour = ((int)(value >> 12)) & 0x1F;
	final int minute = ((int)(value >> 6)) & 0x3F;
	final int second = ((int)(value >> 0)) & 0x3F;
	final Calendar c = Calendar.getInstance();
    c.set(year, month - 1, day, hour, minute, second);
    c.set(Calendar.MILLISECOND, 0);
    final long millis = c.getTimeInMillis();
    return new java.util.Date(millis + (nanos / 1000000));
}

Reply me, please.
Thanks.
Chan.

[shyiko]

Hi @gywndi.

The reason GMT is used is #157 (comment). It's possible that assumption is incorrect. Have you tried enabling DATE_AND_TIME_AS_LONG? Does it make any difference?

[gywndi]

Hi~

I tried enabling DATE_AND_TIME_AS_LONG as you suggested.. But the result is still different.
You can see the different result below.
(This means, I've got still in have same problem..)

---

dt : 1513785969000000
ts : 1513753569000000

MySQL supports micro seconds so it is important issue, I aggree.

However, unlike the TIMESTAMP type, DATETIME is written to the binlog without timezone info. If MySQL is not UTC, the time can be incorrectly calculated as above. It is risky to calculate unix timestamp from DATETIME column without timezone information.

It's better to use date object first and add microsecond info
I think it would be better to use a DATE object to solve timezone issues first, then add micro second information after to solve the microsecond issue.

Thanks.
Chan.

[gywndi]

Hi~

I think the purpose of the existing "UnixTime.from" code is performance.
However, I think that this should not affect the time calculation. I know that the actual Apache NIFI uses this library. I can not trust the data in cases where the MySQL timezone is not UTC.

If there is a purpose for performance as beore, it is necessary to have the logic involved in the DB server's time zone in "UnixTime.from".

Microsecond processing is one of the things I have always been worried about. And I am really grateful for the solution "CompatibilityMode.DATE_AND_TIME_AS_LONG_MICRO".
Now, I can think of a way to process different method, if datetime format comes.

Thanks.
Chan.

[shyiko]

Alright,

MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME.) By default, the current time zone for each connection is the server's time. The time zone can be set on a per-connection basis. As long as the time zone setting remains constant, you get back the same value you store. If you store a TIMESTAMP value, and then change the time zone and retrieve the value, the retrieved value is different from the value you stored. This occurs because the same time zone was not used for conversion in both directions. The current time zone is available as the value of the time_zone system variable. For more information, see Section 10.6, “MySQL Server Time Zone Support”.

passage from https://dev.mysql.com/doc/refman/5.7/en/datetime.html explains it.

I'm afraid #193 (comment) isn't going to work (unless default tz of the jvm instance is matching tz of the connection (or server if not set explicitly) during which DATETIME value was written, that is (like in your case)) (if I understand it correctly). Because DATETIME is (apparently) written "as is" without undergoing conversion to the UTC (and no TZ information is present in the binlog) I'm not sure what the solution might be (except for the obvious - keep all time stored in UTC (which is, again, a generally recommended practice) to avoid all these problems).

[gywndi]

Hi~

"default tz of the jvm instance is matching tz of the connection"
=> I just parse from binlog files not streaming from mysql server.
MySQL and JVM timezone is same to "Asia/Seoul", but this problem happened.

There's no problem on "TIMESTAMP" type.. only happen on "DATETIME" type.
The data DATETIME written only fixed the date information not involved "timezone info".

The focus of my story is that it does not contain time zone information to calculate unix timestamps.

The problem is that if the result of the calculation is an integer value of the exact date, there is no issue, but the date is different from the date stored in the database.
There's no database connection if I use only binlog file. :-)

Thanks.
Chan.

[gywndi]

Additionally, I think it’s not a good idea to force UTC on mysql server to solve this kind of problem.
Thanks.
Chan.

[gywndi]

Hi~

If service works only with UTC as you said, there's no problem ... but no one can force the configuration of the DB server.

The problem is that the DB server writes down DATETIME information to the binlog on the database's timezone basis.

I agree with your unixtimestamp calculation.
However, at the time of DATETIME calculation, (at least) the parameters of the timezone of the db server should be considered. (or, depending on jvm's time zone for everything...)
I think timezone must be same jvm and mysql.

Same time, but different different result. This must be a big risk.

Thanks.
Chan.

[gywndi]

I think this solution is reasonable not to disturb your logic to much.
There's nothing happen, if both server and jvm has same timezone "UTC".
How about this??

[gywndi]

Hi~

I think server timezone setting must be seperate from JVM's.
So, I added EventDeserializer.setServerTimeZone(String tzId) method to calculate unix_timestamp base on MySQL Timzone.
This means DATETIME/DATE would be calculated base on "Asia/Seoul".
(Must be same as MySQL's timezone)

EventDeserializer eventDeserializer = new EventDeserializer();
eventDeserializer.setChecksumType(ChecksumType.CRC32);
eventDeserializer.setServerTimeZone("Asia/Seoul");

This would display base on UTC.

## JVM Timestamp
static{
    TimeZone.setDefault(TimeZone.getTimeZone("UTC"));
}

You can check from my fork project, as below.

master...gywn-net:master

What's your opinion?
My case is good. :-)
Reply, please.

Thanks.
Chan.

[gywndi]

Hi~

Please give feedback on the solutions I have presented.
Depending on the time zone, I think that it is obviously an issue that the wrong time can be output in case of DATETIME.

Of course, I can use it personally with forked project, but I do not want to get out of the open source flow.

Thanks.
Chan.