Timestamp timezone issue

[Hackeruncle]

Maxwell version: 1.10.5~1.13.1 (tested)
Current Maxwell version: 1.10.8

1.MySQL: create test table
CREATE TABLE test.timezonetest (
id int(11) AUTO_INCREMENT primary key,
date_c date DEFAULT NULL,
datetime_c datetime DEFAULT NULL,
timestamp_c timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

2.Maxwell: config.properties
host=192.168.17.38
user=maxwell
password=xxxxxxx
jdbc_options = useSSL=false

include_dbs=test
include_tables=timezonetest

3.Test1:***********************************
3.1.Test
MySQL SQL: insert into timezonetest(date_c,datetime_c,timestamp_c) values('2018-02-25','2018-02-25','2018-02-25 10:00:00');

3.2.Run Maxwell producer as stdout
Maxwell: {"database":"test","table":"timezonetest","type":"insert","ts":1519457490,"xid":34205,"commit":true,"data":{"id":3,"date_c":"2018-02-25","datetime_c":"2018-02-25 00:00:00","timestamp_c":"2018-02-25 02:00:00"}}
The timestamp_c minus 8 hours, is wrong.

4.Test2:***********************************
4.1.ADD serverTimezone in config.properties
jdbc_options = useSSL=false&serverTimezone=Asia/Shanghai

4.2.Test
MySQL SQL: insert into timezonetest(date_c,datetime_c,timestamp_c) values('2018-02-25','2018-02-25','2018-02-25 10:00:00');

4.3.Run Maxwell producer as stdout
Maxwell : {"database":"test","table":"timezonetest","type":"insert","ts":1519457490,"xid":34205,"commit":true,"data":{"id":3,"date_c":"2018-02-25","datetime_c":"2018-02-25 00:00:00","timestamp_c":"2018-02-25 02:00:00"}}
The timestamp_c minus 8 hours, is also wrong.

5.Test3:***********************************
5.1 Copy DateTimeColumnDef.java and DateFormatter.java files, change to TimeStampColumnDef.java and TimeStampFormatter.

5.2 ColumnDef.java
[original]:

 		 case "datetime":
		 case "timestamp":
		 return new DateTimeColumnDef(name, type, pos, columnLength);

[current]:
case "datetime":
return new DateTimeColumnDef(name, type, pos, columnLength);
case "timestamp":
return new TimeStampColumnDef(name, type, pos, columnLength);
Code zip:
timestamp with timezone.zip

5.3.Test
MySQL SQL: insert into timezonetest(date_c,datetime_c,timestamp_c) values('2018-02-25','2018-02-25','2018-02-25 10:00:00');

5.4.Run Maxwell producer as stdout
Maxwell: {"database":"test","table":"timezonetest","type":"insert","ts":1519457490,"xid":34205,"commit":true,"data":{"id":3,"date_c":"2018-02-25","datetime_c":"2018-02-25 00:00:00","timestamp_c":"2018-02-25 10:00:00"}}
The timestamp_c column is correct.

[saup007]

@Hackeruncle Modify the source code, recompile？

thanks.
cp ../timestamp.with.timezone/* ./src/main/java/com/zendesk/maxwell/schema/columndef/
mvn clean compile
mvn package -DskipTests=true

mkdir lib
cp -r ./target/lib/* lib/
cp ./target/maxwell-1.13.1.jar lib/

done.

[wavefly666]

@Hackeruncle but couldn't find MySQLConstants jar?

[t3link]

I also have this problem. Is there any resolution without modify the source code?
I have set jdbc_options or replication_jdbc_options to "serverTimezone=Asia/Shanghai&zeroDateTimeBehavior=convertToNull&connectTimeout=5000".
But neither can get the right GMT+8 timestamp string.

[chenbo29]

I also have this problem. Is there any resolution without modify the source code? I have set jdbc_options or replication_jdbc_options to "serverTimezone=Asia/Shanghai&zeroDateTimeBehavior=convertToNull&connectTimeout=5000". But neither can get the right GMT+8 timestamp string.

do you resolve?

[pengweizhong]

Great, this helped me

[pengweizhong]

I found that there seems to be a simpler solution, which is to modify the member attribute UTC_ZONE of the class com.zendesk.maxwell.schema.columndef.DateFormatter;
turn out to be:

private static final TimeZone UTC_ZONE = TimeZone.getTimeZone("UTC");

Now:

private static final TimeZone UTC_ZONE = TimeZone.getTimeZone("GMT+8");

This can also solve the problem.

If you like, you can write the time zone configuration in config.properties, so that the time zone will be more flexible. Then assign it in the main startup class. The lazy loading feature of ThreadLocal.withInitial supports this solution very well.

//DateFormatter
	//	private static final TimeZone UTC_ZONE = TimeZone.getTimeZone("UTC");
	private static TimeZone UTC_ZONE;
	private static final ThreadLocal<Calendar> calendarThreadLocal = ThreadLocal.withInitial(Calendar::getInstance);
	private static final ThreadLocal<Calendar> calendarUTCThreadLocal = ThreadLocal.withInitial(() -> Calendar.getInstance(UTC_ZONE));
	public static void initZone(TimeZone timeZone) {
		if (UTC_ZONE != null) {
			return;
		}
		synchronized (DateFormatter.class) {
			UTC_ZONE = timeZone;
		}
	}

//Maxwell
			// ... ...
			MaxwellConfig config = new MaxwellConfig(args);
			TimeZone timeZone = config.getTimeZone();
			DateFormatter.initZone(timeZone);
			if (config.log_level != null) {
				Logging.setLevel(config.log_level);
			}
			//... ...

//MaxwellConfig
	//add this
	@Getter
	public TimeZone timeZone;