Pitfalls with JDBC PreparedStatement.setTimestamp/ResultSet.getTimestamp

Contents

1. Situation
2. Explanation
3. Solutions
   1. The Ugly
   2. The Bad
   3. The Good
4. Examples

Situation

Imagine the following Java application:

• it stores timestamp data in a DB via JDBC and reads it back;
• in the DB timestamps are represented as timestamp [without time zone] SQL data type (the Java SE API counterpart is JDBCType.TIMESTAMP);
• in the application timestamps are represented as Timestamp. They are created via the constructor Timestamp(long time) and hence are expected to represent Java Time-Scale¹. Values are bound to a PreparedStatement via PreparedStatement.setTimestamp(int parameterIndex, Timestamp x) and retrieved from a ResultSet via ResultSet.getTimestamp(int columnIndex).

It is working fine—stores timestamps and reads them back as expected. But then you start it on a different machine all of a sudden, it reads not what you expected—all timestamps are shifted by a few hours. What happened and how this could have been avoided?

Explanation

Turns out, the new machine uses a different time zone, but our application was not written correctly to survive such an event.

The first pitfall we fell into is that our imaginary application uses the method PreparedStatement.setTimestamp(int parameterIndex, Timestamp x), which in turn uses the default time zone of the Java virtual machine (JVM) to construct SQL timestamp value for the DB, which is not something I would expect. This is a documented JDBC behaviour, but it is documented only for the method PreparedStatement.setTimestamp(int parameterIndex, Timestamp x, Calendar cal) for some reason. As a result, the value our application writes to the DB depends not only on the number of milliseconds we use to create a Timestamp but also on the default time zone.

The second mistake is using timestamp SQL data type instead of using timestamp with time zone. If the DB does not store time zone information, the method ResultSet.getTimestamp(int columnIndex) uses the default time zone to construct a Timestamp. This is not documented but is true and may be guessed from the specification of the method ResultSet.getTimestamp(int columnIndex, Calendar cal). Because of this, the Timestamp our application reads from the DB depends not only on the value stored there but also on the default time zone.

What is even more surprising is that in case of using timestamp with time zone, the method PreparedStatement.setTimestamp(int parameterIndex, Timestamp x) stops using the default time zone. I do not know how JDBC API users are supposed to figure this out without failing at first and then debugging and experimenting.

Solutions

The Ugly

Use the methods PreparedStatement.setTimestamp(int parameterIndex, Timestamp x, Calendar cal)/ResultSet.getTimestamp(int columnIndex, Calendar cal) with explicitly specified Calendar objects constructed with the same time zone. This solution is ugly because once the data is stored in the DB its time zone information is still lost and no one but the writer at best knows what it is.

The Bad

Use the SQL data type timestamp with time zone (the Java SE API counterpart is JDBCType.TIMESTAMP_WITH_TIMEZONE). This way you not only preserve the time zone information, but also can use more straightforward methods PreparedStatement.setTimestamp(int parameterIndex, Timestamp x)/ResultSet.getTimestamp(int columnIndex). This solution is bad because the class Timestamp is mutable, which arguably makes the code less robust at the cost of flexibility to reuse the same object for different values, which may in theory be justified by reasons related to performance but in practice is unreasonable in situations when JDBC is used.

The Good

JSR 221 JDBC API Specification 4.2 introduced, besides others, the following two bidirectional mappings (see sections "B.4 Java Object Types Mapped to JDBC Types", "B.5 Conversions by setObject and setNull from Java Object Types to JDBC Types", "B.6 Type Conversions Supported by ResultSet getter Methods" in the specification):

• OffsetDateTime—JDBCType.TIMESTAMP_WITH_TIMEZONE
• LocalDateTime—JDBCType.TIMESTAMP

The methods PreparedStatement.setObject(int parameterIndex, Object x)/ResultSet.getObject(int columnIndex, Class<T> type) can now be used to write/read OffsetDateTime/timestamp with time zone. This is the best solution we may have because it uses immutable OffsetDateTime, the time zone information is not lost, and the behavior does not depend on the default time zone.

Examples

Junit 5 tests illustrating the problem and the solutions: JdbcTimestampItTest.java.