dbunit Merge Request #10: XlsTable#getDateValue adds timezone offset unnecessary. (merged)

Problem

When I store the datetime value with XlsDataSet into PostgreSQL's timestamp column, the timestamp value has extra time.

e.g.)
In my Asia/Tokyo (+09:00) timezone environment,
when .xls cell value (date format) is 01/01/1970 00:00:00,
timestamp value stored in postgresql will be 01/01/1970 09:00:00.

I think this problem is identical with issue 280 and issue 371.

I checked this using PostgreSQL 9.4.5 (Windows7 64bit or Windows10 64bit or CentOS7.1 64bit)

Cause

Because the XlsTable#getDateValue(Cell) method adds the timezone offset, the value stored in PostgreSQL has extra value (=timezone offset).

How to fix

Comment out the lines below in XlsTable#getDateValue(Cell) works fine for me.

// Add the timezone offset again because it was subtracted automatically by Apache-POI (we need UTC)
long tzOffset = TimeZone.getDefault().getOffset(date.getTime());
date = new Date(date.getTime() + tzOffset);

I think it is not necessary to add the timezone offset.

For example, when .xls cell value in date format is 01/01/1970 00:00:00,
POI returns java.util.Date of 01/01/1970 00:00:00+09:00.
Then java.util.Date#getTime() returns -32400000 because this method returns UTC value.
And then, this value converted to java.sql.Timestamp in TimestampDataType.typeCast(Object). This results in timezone specific value 01/01/1970 00:00:00+09:00. So value stored in PostgreSQL is 01/01/1970 00:00:00 (timestamp without timezone) or 01/01/1970 00:00:00+09:00 (timestamp with timezone).

[Jeff Jensen]

Thanks for the merge request.
I'm wondering if a recent data type change may resolve the issue, since it appears when saving to the DB.

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

[Ryota Sato]

Thank you for your reply.
I will try out 2.5.3-SNAPSHOT and then provide feedback.

[Ryota Sato]

I tried out 2.5.3-SNAPSHOT.
It works fine when I wrote timestamp like '2015-01-01 10:00:00 +0100' in string cells.

And sorry, I took misquotations (#304 and #380).
What the issue I think is when we write timestamp in the numeric cell, the timestamp automatically substracted/added timezone offset.
This is not ideal when our application programs are available in one timezone only.

I rethought about this issue.
What I rethought is written in the following GIST.
https://gist.github.com/Monota/d62ed3fc9ab7e210b129daa86bc7ed1f

My repository for survey is the following:
https://github.com/Monota/spring-dbunit-timezone-survey

And how to tackle it? In the case of numeric cell, I think...
If an application program is available in one timezone only, dbunit should not substract/add timezone offset auotmatically.If an application program is available in the worldwide, dbunit should substract/add timezone offset auotmatically.
I think to add a boolean property to XmlDataSet which indicates substract/add timezone offset automatically or not will be the best (and default setting should not change the behavior of the current version).

What do you think about that?

Many thanks.

[Ryota Sato]

I wrote a test to explain this issue.

https://github.com/Monota/spring-dbunit-timezone-survey/blob/master/src/test/java/com/example/monota/spring/service/DbUnitDateTest.java

(I cannot see your reply here but I recieved an email of your reply from sf.)

[Jeff Jensen]

Thanks again for the merge request and extra research.
Excel does not have concept of timezones; I'm not sure why the code was adjusting for it.
Issue [bugs:#371] adds a test showing this problem so have merged this and committed the test.

Please test the updated snapshot and reply with your results!

[Ryota Sato]

All my tests passed.
Thank you so much!

[Jeff Jensen]

Excellent! Thank you for verifying so quickly and again for the help!