Introduction:

Dates and times are an area of software that seem easy until you actually work with them, especially across timezones and when databases and Java are involved. We created DbAssist to make it a little bit easier.

The article presents a clean and simple way to solve the well-known issue with JDBC adding improper time shift to dates, when saving to or reading from a database. The issue does not occur at all on condition that all the components we are using in the project (servers, clients etc.) are set up in the same time zone. If at least one component has a different time zone (for example, we want to store dates in a database as UTC0, but leave the application servers in local time zone), then we experience unexpected time shift whenever we perform read/write operation. The solution differs depending on whether we use JPA Annotations or Hibernate Mapping files (.hbm) to define entities and their relations in the database.

The project source code including all the fixes and the associated unit tests for them is stored in the repository on github: https://github.com/montrosesoftware/DbAssist . The provided unit tests should be treated also as an example of how to use the classes provided with DbAssist-jpa-commons library.

The problem

First, let’s clarify (and prove that it exists!) the problem itself. Suppose our system has a user type. We want to save user instances to a database and read them back exactly as they were. To simplify the code example and eliminate any doubts, we inserted the example user record into the database manually using the following SQL query:

View the code on Gist.

The SQL query is self-explanatory. The created_at_utc value corresponds to the date and time when we inserted the record into the database. We want this value to be in the UTC timezone so the system behaves the same regardless of where it runs, which is why we store it in the following examples as a java.util.Date . Oracle says:

…the Date class is intended to reflect coordinated universal time (UTC)… .

Perfect! Before we try to persist such entity with an ORM such as Hibernate, let’s see what happens when we simply read the data using plain old JDBC. We are testing with Microsoft SQL Server, but results are similar on PostgreSQL and SAP (Sybase) ASE.

Example using JDBC

Reading the record with UTC date using plain JDBC

View the code on Gist.

Program results (the code run in different timezones)

Result of running the code in New York timezone (EST, UTC-5, set by passing -Duser.timezone=America/New_York to JVM)

Dates comparison result: Expected: Wed Dec 31 19:00:00 EST 1969 Expected as UTC0: 1 Jan 1970 00:00:00 GMT Expected as long: 0 Actual: Thu Jan 01 00:00:00 EST 1970 Actual as UTC0: 1 Jan 1970 05:00:00 GMT Actual as long: 18000000 1 2 3 4 5 6 7 8 Dates comparison result : Expected : Wed Dec 31 19 : 00 : 00 EST 1969 Expected as UTC0 : 1 Jan 1970 00 : 00 : 00 GMT Expected as long : 0 Actual : Thu Jan 01 00 : 00 : 00 EST 1970 Actual as UTC0 : 1 Jan 1970 05 : 00 : 00 GMT Actual as long : 18000000

The same code run in Warsaw timezone (CET, UTC+1, set by passing -Duser.timezone=Europe/Warsaw to JVM):

Dates comparison result: Expected: Thu Jan 01 01:00:00 CET 1970 Expected as UTC0: 1 Jan 1970 00:00:00 GMT Expected as long: 0 Actual: Thu Jan 01 00:00:00 CET 1970 Actual as UTC0: 31 Dec 1969 23:00:00 GMT Actual as long: -3600000 1 2 3 4 5 6 7 8 Dates comparison result : Expected : Thu Jan 01 01 : 00 : 00 CET 1970 Expected as UTC0 : 1 Jan 1970 00 : 00 : 00 GMT Expected as long : 0 Actual : Thu Jan 01 00 : 00 : 00 CET 1970 Actual as UTC0 : 31 Dec 1969 23 : 00 : 00 GMT Actual as long : - 3600000

Conclusion

We inserted a record with date Zero (aka Unix Epoch time, 1970-1-1... ) manually into the database. Then pulled it back out twice, each time with a client application run on JVM in a different timezone. In other words, we simulated a situation, when server with the DB stores dates as UTC, while the applications reading from that DB are located in multiple locations around the world.

With what result? Well, not the promising one. As we look at the actual retrieved dates for both exemplary timezone runs, they seem to be read correctly only at first glance. In fact, the abbreviation indicating the timezone changes everything: in first case it is Thu Jan 01 00:00:00 EST 1970 , in second Thu Jan 01 00:00:00 CET 1970 . EST and CET timezones are 6 hours away from each other.

Then we tried to show what is the underlying long number for each date, so that we make sure that what we see is a problem with JDBC, not with some implicit toString date conversion. It only confirmed our concerns. The result of getTime , a number of milliseconds since the Unix Epoch time shows 18000000 in New York case and -3600000 in the second one. We just read the same date using applications located in two different places in the world and received two completely different dates. We don’t have to explain how bad news is that.

So, what’s going on?

Quick explanation of the problem

The reason why we face the time zone shift is because when JDBC transfers a date from (or to) the SQL date type to the java.util.Date type, it by default treats that date as a local one (by local we mean the JVM default time zone). What is more, such a situation takes place even if the DB contains information about the date timezone. It means that whatever dates we are trying to read from/write to a DB will be misinterpreted if the local time zone is different than the timezone in which the dates are stored.

One would think that maybe Hibernate tackles this issue in a correct way. Let’s check it.

Example using Hibernate

The entity user remains the same as in the previous simple JDBC example. The difference is that now it gets structured by a class User:

Entity class

View the code on Gist.

Again, we want to store our dates in UTC, but the application servers are in a different timezone. We configure Hibernate like this:

HBM file

View the code on Gist.

Simple read date in different timezones example

View the code on Gist.

This example is pretty straightforward. We repeat what we did in JDBC example, but with Hibernate ORM. First, we write one user record into the DB (using plain SQL) and then read it using Hibernate’s createCriteria . The output shown in the console after running the code was:

Time in ms: 1465754055000 1 2 Time in ms : 1465754055000

Then, we changed the time zone of our OS from CEST to EDT and run the code again. Again, the result is quite surprising:

Time in ms: 1465736055000 1 2 Time in ms : 1465736055000

Just like in JDBC example, we can see that both numbers are different. It means that they represent different ‘points’ in time, even though in both cases we manually saved exactly the same date to the DB. Okay, so reading dates using Hibernate did not fix the problem after all…

The surprising and potentially dangerous implications of this issue is that we would read a completely different date (a number of milliseconds since the aforementioned date 0) for every single time zone. In such case, it would be impossible to safely rely on the dates kept in the DB in a system interacting with applications in multiple locations from different time zones in the world. Anyway, we can see that reading UTC dates correctly from the DB is not that easy. How about saving one to the DB?

Write a UTC0 date into a DB

View the code on Gist.

In the example above, we are saving the same 9:54 UTC0 date into a DB using two approches: one using Hibernate and another using native SQL query. What we expect to be saved into a DB is two User entities the times of which are equal to 9:54 (their only difference should be id ).

The actual data saved into the DB is quite different though:

As we can see, Hibernate failed to save the date time correctly (first record), while the date inserted by a plain SQL query (second record) is correct. So what happened? Since our location is in UTC+2 time zone, the local time of such a date is 11:54 UTC+2. However, when we save it to the DB using Hibernate, thanks to JDBC the DB receives the date as it is (11:54). From the point of view of DB, we just saved a 11:54 UTC0 date, which of course is not a correct result.

In case of plain SQL, we are saving a correct 9:54 date String and it is exactly what is saved to the DB. This is why even though we wanted to save the same dates into the DB, we ended up with two different ones. Again, in such case, we can easily end up with a system where the dates we saved into the DB would be incorrectly affected by the varying time zone shifts, leaving us with unreliable and inconsistent data.

As we can see in previous examples, pure JDBC failed and Hibernate failed too. So, how to solve this problem?

Our solution

We would like to present our approach to fix the time zone shift which we believe handles it in an elegant and universal way.

Partial solution

In order to make sure Hibernate does not fail to read/write dates properly, we forced him to treat the date types from SQL as our custom UTC date type, which extends the suitable type from Hibernate. The fix differs a bit depending on whether we use JPA Annotations or Hibernate .hbm files, but the essence of the solution is quite straightforward: we are creating a layer between Hibernate and JVM which overrides the setters and getters of such a type so that the dates are interpreted correctly by JDBC.

To illustrate it, let’s look at the following date type class used in .hbm approach fix:

View the code on Gist.

To instruct Hibernate to use this type, we use the following line inside the entity .hbm file:

View the code on Gist.

In case of JPA annotations, we use a different date type class, because we override the different methods. Also, we use @TypeDef annotations to map standard date types into our custom ones:

View the code on Gist.

That being said, in order to actually apply the fix in case of JPA Annotations, all you have to do is to add Maven dependency to the fix and set your app configuration to scan specified fix package (see link for instructions).

In case of most versions, the fix is a bit too long to include in this short article, but the idea is similar. It involves creating a class overriding the AbstractSingleColumnStandardBasicType<Date> abstract class and overriding getBinder and getExtractor methods so that Hibernates treats dates as UTC. The full code for the fix is on project’s github.

Full solution

The fixes presented in the previous section do not work in one case: when we use Hibernate with Spring Specification class to specify the SQL WHERE conditions. The problem is that when we create predicates in a standard way, Hibernate applies misinterpreted date values to build SQL query. Therefore we need to separately specify WHERE conditions and later assign proper date values into the query using TypedQuery.setParameter method.

The commented out part shows the standard way to use it. As mentioned before, the standard way would fail and add improper time shift to utcDate :

View the code on Gist.

Unfortunately, using such a fix makes the code less readable and more difficult to maintain. Therefore we recommend using the next option.

In order to make our solution complete, we introduced DbAssist-jpa-commons library with the classes ConditionsBuilder and AbstractRepository which allow us to easily create complex logical combinations of conditions and at the same time handle necessary time zone conversions.

The code below shows how to use ConditionsBuilder class to achieve exactly the same result as in the example above:

View the code on Gist.

Summary

The article presents our approach to fix popular JDBC time shift issue and also introduces our custom conditions library, which allows us to easily apply complex conditions on the query. It handles all the necessary time zone handling between JVM and DB automatically, so there is no need to do it manually. In addition, the whole library is open source and offers much easier way to control the conditions we want to put on the query.

It is worth mentioning the fact that the library has been successfully deployed and used in a project we have been developing for one of our biggest banking clients. It proved itself to be reliable and easily extensible when needed.

The full code with usage examples can be found on our github. If you just want to install the fix, follow the instructions on wiki homepage. From the same website, you can access more examples and a tutorial for DbAssist-jpa-commons library .

Supported versions

Hibernate

The list of supported Hibernate versions and their fix counterparts is in the table below:

| Hibernate version | HBM | JPA | | ----------------- |----------------------|------------------| | 3.3.2.GA | DbAssist-hbm-3.3.2 | N/A | | 3.6.10.Final | DbAssist-hbm-3.6.10 | N/A | | 4.2.21.Final | DbAssist-4.2.21 | DbAssist-4.2.21 | | 4.3.11.Final | DbAssist-4.3.11 | DbAssist-4.3.11 | | 5.0.10.Final | DbAssist-5.0.10 | DbAssist-5.0.10 | | 5.1.1.Final | DbAssist-5.1.1 | DbAssist-5.1.1 | | 5.2.2.Final | DbAssist-5.2.2 | DbAssist-5.2.2 | 1 2 3 4 5 6 7 8 9 10 | Hibernate version | HBM | JPA | | -- -- -- -- -- -- -- -- - | -- -- -- -- -- -- -- -- -- -- -- | -- -- -- -- -- -- -- -- -- | | 3.3.2.GA | DbAssist - hbm - 3.3.2 | N / A | | 3.6.10.Final | DbAssist - hbm - 3.6.10 | N / A | | 4.2.21.Final | DbAssist - 4.2.21 | DbAssist - 4.2.21 | | 4.3.11.Final | DbAssist - 4.3.11 | DbAssist - 4.3.11 | | 5.0.10.Final | DbAssist - 5.0.10 | DbAssist - 5.0.10 | | 5.1.1.Final | DbAssist - 5.1.1 | DbAssist - 5.1.1 | | 5.2.2.Final | DbAssist - 5.2.2 | DbAssist - 5.2.2 |

Microsoft JDBC SQL Driver

4.0

4.1

4.2

Alternative approaches

There are a couple of alternative approaches that you could use to fix the time shift issue. We will quickly introduce them below.

Using varchar

One solution, commonly mentioned on for example StackOverflow, involves storing the dates inside the DB as varchar . It requires explicit conversion from String to Date and vice versa for read/write.

Setting the default timezone

Some Hibernate users propose a quick fix for these issues. Namely, they propose changing the default time zone of JVM for the entire app to UTC0. It means that the local dates are equal to UTC0 dates in JVM, so we get rid of the read/write date time shift.

However, we do not consider this fix as a good programming practice. It requires meddling in the application JVM settings, which generally is not a great idea and may cause unexpected problems in the future.

Setting up JDBC time zone

Some DB engines (e.g. MySQL) allow us to pass a specific time zone to a JDBC driver in order to force it to automatically perform time shift conversions.

Annotations inside entity

This approach is very similar to ours and can be achieved by creating a custom UTC date type and annotating each Date field in the entity classes with the following code:

@org.hibernate.annotations.Type(type="CustomUtcDateType") 1 2 @ org . hibernate . annotations . Type ( type = "CustomUtcDateType" )

The disadvantage of this approach is that we have to modify each entity class so that it maps all Date fields.

Using hibernate-8java for Hibernate 5 and Java 8

If working with Hibernate 5 and Java 8, it is possible to use the hibernate-8java Maven dependency which provides the mapping between the standard SQL Date/Time and the supported Java 8 Date/Time classes.

For more details refer to link.

Source of the featured image: Salvador Dali, The Persistence of Memory, Oil on canvas, 1931