How to Specify the Offset of a Timestamp in a DbUnit Data Set

This blog post tells the story of a failing integration test, identifies the problem that failed the test, and describes how we can solve that problem. After we have finished this blog post, we

  • Can identify the problems that we can face when we use timestamps in DbUnit data sets.
  • Understand how we can specify the offset of a timestamp in a DbUnit data set.

Let's begin.

The Story of a Failing Integration Test

Last week I was writing integration tests for code that saves a timestamp to the database in UTC time zone. Unfortunately, one of my integration tests failed because of this error:

value (table=exams, row=0, col=start_time) 
expected:<2019-01-15 1[2:00:0]0> but was:<2019-01-15 1[4:00:00.]0>

At first I thought that my code had a bug because it seemed that the timestamp was saved to the database in local time zone. However, when I took a look at the database (we run our integration tests against a PostgreSQL database), I noticed that the timestamp was saved in UTC.

That's why I came to the conclusion that DbUnit must transform this timestamp into the local time zone when it loads the timestamp from the database, and my integration test fails because my DbUnit data set was using the UTC time zone. At this point I had two options:

  • I could take the easy way out and use the local time zone in my DbUnit data set.
  • I could try to find a way to use the UTC time zone in my DbUnit data set.

I didn't want to use to the first option because of these reasons:

  • I wanted that my DbUnit data sets use the same time zone as the data found from the database because this makes my tests easier to read.
  • You cannot use the local time zone if you have to run your tests in multiple time zones.

In other words, it was time to put Google to work. At first I found a couple of solutions that don't solve my problem because my REST API has to return the timestamps in local time. After a couple of hours I found this DbUnit fork and the TimestampDataTypeTest class.

When I was reading the source code of the TimestampDataTypeTest class, I realized that I just found the solution to my problem. Next, I will describe how we can specify the offset of a timestamp in a DbUnit data set.

Adding the Offset of a Timestamp to a DbUnit Data Set

If we want to specify the offset of a timestamp in a DbUnit data set, we have to specify our timestamp by using the syntax: [timestamp] [offset]. Let's take a look at three examples that demonstrate how we can specify the offsets of our timestamps.

These examples contain DbUnit data sets which specify the start time of an exam. The start time of an exam is: 2019-01-15 12:00:00 (UTC).

First, if we want to subtract time from the UTC time, we have to specify the offset by using the syntax: -[subtracted time]. For example, if we want to subtract one hour from the UTC time, we have to specify the offset by using the string: -0100.

The DbUnit data set that specifies the start time of our exam looks as follows:

<dataset>
	<exams id="1"
       	   start_time="2019-01-15 11:00:00 -0100"/>
</dataset>    

Second, if we want add time to the UTC time, we have to specify the offset by using the syntax: +[added time]. For example, if we want to add one hour to the UTC time, we have to specify the offset by using the string: +0100.

The DbUnit data set that specifies the start time of our exam looks as follows:

<dataset>
	<exams id="1"
       	   start_time="2019-01-15 13:00:00 +0100"/>
</dataset>    

Third, if we want to use the UTC time, we have to specify the offset by the string: +0000. The DbUnit data set that specifies the start time of our exam looks as follows:

<dataset>
	<exams id="1"
       	   start_time="2019-01-15 12:00:00 +0000"/>
</dataset>    

We can now specify the offset of a timestamp in a DbUnit data set. Let's summarize what we learned from this blog post.

Summary

This blog post has taught us four things:

  • If we want to specify the offset of a timestamp in a DbUnit data set, we have to specify our timestamp by using the syntax: 2019-01-15 12:00:00 [offset].
  • If we want to subtract time from the UTC time, we have to specify our timestamp by using the syntax: 2019-01-15 12:00:00 -[subtracted time].
  • If we want to add time to the UTC time, we have to specify our timestamp by using the syntax: 2019-01-15 12:00:00 +[added time].
  • If we want to use the UTC time, we have to specify our timestamp by using the syntax: 2019-01-15 12:00:00 +0000.
1 comment… add one

Leave a Reply