Handling Local vs Remote Database TimeZone Differences in Tests

Question

I have unit test in Java that writes a constant Timestamp to a row in my local test database, reads it back and compares it to what I expected. This works fine on my local laptop which is under the GMT timezone.

When I commit the code to our continuous integration server, the test fails with the different in time being -5 hours. This is no surprise since our integration server is hosted on AWS on the East Coast of US. However, it is causing a problem...

Short of changing my local MySQL server to have the same timezone as the remote server (and having all the developers in my team do so also), can anyone show me how to fix this issue in code without getting too hacky?

//Fetch actual table contents
IDataSet databaseDataSet = databaseTester.getConnection().createDataSet();
ITable actualTable = databaseDataSet.getTable("batch");

// Load expected data from an XML dataset
IDataSet expectedDataSet = new XmlDataSet(getClass().getResourceAsStream("/dbunit/expected_insert_batch.xml"));
ITable expectedTable = expectedDataSet.getTable("batch");

// Assert actual database table match expected table
Assertion.assertEquals(expectedTable, actualTable);

Thanks,

You should detail how you compare timestamps, as integers? as strings? — Guillaume, Dec 14 '10 at 11:56

score 4 · Accepted Answer · answered Dec 14 '10 at 11:57

4

You can set a timezone for the MySQL server separate from the OS timezone, or even for individual DB sessions. The former is IMO preferable, as is using UTC everywhere except for the UI and when importing data.

answered Dec 14 '10 at 11:57

Michael Borgwardt

342,105
78
482
720

score 1 · Answer 2 · answered Dec 14 '10 at 12:00

1

I suggest you make all your systems use the same time zone e.g. UTC/GMT+0 and only use the timezone when displaying to a user or in reporting.

answered Dec 14 '10 at 12:00

Peter Lawrey

525,659
79
751
1,130

score 1 · Answer 3 · answered Dec 14 '10 at 12:46

1

OK, this may not be the best option, but why not create another

"/dbunit/expected_insert_batch.xml"

for your CI server. Then add a switch in your unit test for timezone.

answered Dec 14 '10 at 12:46

Samuel Parsonage

3,077
1
18
21

score 0 · Answer 4 · edited May 23 '17 at 12:26

0

If you're creating the timestamp in Java, I recommend using a mock so that it isn't system-dependent at all.

See the answers to this question for ideas.

edited May 23 '17 at 12:26

Community

1
1

answered Dec 14 '10 at 12:03

Don Roby

40,677
6
91
113

But my unit test is testing that the DAO writes to the database correctly. I am already writing a constant timestamp thanks to a mock, but the problem is that it has to be written via the DB to test - and if the timezone is different as described it causes a problem... – Scruffers Dec 14 '10 at 12:08
For that problem, I'd advocate as others have answered, just keep all database times in UTC and use timezone for display only. – Don Roby Dec 14 '10 at 12:17

score -1 · Answer 5 · answered Dec 14 '10 at 14:37

-1

You need to make your test not depend on environment. Look for places where you can make this field dynamic and it should work anywhere.

answered Dec 14 '10 at 14:37

Jinesh Parekh

2,131
14
18

A DAO test will always depend on the database it runs on, but I agree in general tests should be environment agnostic. – Scruffers Dec 15 '10 at 14:55

Handling Local vs Remote Database TimeZone Differences in Tests

5 Answers5