#371 Loading XLS datasets with time values creates wrong data in MySQL DB

[MSA]

Hello,

this is most likely related to bug #280 - at least the same fix worked for me here; however, there might be a better or more appropriate place to fix this.

Here is my problem:
I have an XLS file with a datetime value (e.g. '2015-08-01 07:24:56').
Having dbunit load this value into a MySQL datetime or timestamp column causes the value stored in the MySQL table to be off by whatever the JVM assumes to be the current timezone's offset.
So for me with PDT (i.e. -07:00) being the current timezone; this creates a time of 00:24:56 rather than 07:24:56 in the database.

Considering that both Excel sheets as well as MySQL datetime types do not have any timezone component, this is definitely wrong.

For timestamp columns (as compared to datetime columns), MySQL does an internal conversion from UTC to session timezone when reading and from session timezone to UTC when writing. But as long as both sessions (i.e. the JDBC session that writes the value and the MySQL Workbench session that reads the rows) use the same session timezone, there should not be a difference between what is stored in the XLS file and what is displayed in the SQL client after dbunit has inserted the records.

[Jeff Jensen]

Thanks for the report.
Issue 380 https://sourceforge.net/p/dbunit/bugs/380/ , which points to a fix in issue 304 https://sourceforge.net/p/dbunit/bugs/304/ , deals with non-local timezones.

I committed this fix and published a 2.5.3-SNAPSHOT. Do you have a moment to test it and reply whether it fixes the problem or not please?

Snapshot repo info here:
http://dbunit.sourceforge.net/repos.html

[MSA]

No, issue still persists with 2.5.3-SNAPSHOT.
I am attaching a test case and respective XLS file (will have to add this in a spearate comment as the web interface lets me pick only one attachment per comment).

The test case contains some additional explanation.

[MSA]

Here is the test file...

[MSA]

I fixed this in a local copy of DBUnit 2.5.1 according to http://dbunit.996259.n3.nabble.com/Datetime-Time-Zone-td661.html

This works for me and fixes the previously attached test case. Not sure what side-effects this might have, but it does not break any of the other DBunit test cases except for the one directly related to the modified method.

[Jeff Jensen]

Thank you! This is a very helpful test showing the problem.
I am committing it and a pull request that makes the test pass.

[Jeff Jensen]

Excel does not have concept of timezones; I'm not sure why the code was adjusting for it.

Please test the updated snapshot and reply with your results!

[MSA]

Current snapshot seems to have fixed the problem.

[Jeff Jensen]

Thank you for testing!