UTC, SQL Server, and Spring

I’ve recently been introducing the Spring Framework into an existing Java application, using it to speed up adding new features, while making the existing JDBC code more maintainable. One tricky area has been time handling: the application uses an older SQL Server version, so cannot take advantage of the implementation of timestamp with time zone in SQL Server 2008. All the time fields are kept in UTC, and the application must be careful that all the times are converted to and from UTC correctly. With pure JDBC this is handled explicitly, but with Spring JDBC access this is implicit.

In pure JDBC this can be handled by using a Calendar object in the correct time zone:

Calendar cal = java.util.Calendar.getInstance();
java.util.TimeZone tz = java.util.TimeZone.getTimeZone("UTC");
cal.setTimeZone(tz);
PreparedStatement ps = conn.prepareStatement("...");
Date date = new Date();
ps.setTimestamp(1, date, cal);

The obvious way of using Spring’s simple JDBC template doesn’t work:

Calendar cal = java.util.Calendar.getInstance();
java.util.TimeZone tz = java.util.TimeZone.getTimeZone("UTC");
cal.setTimeZone(tz);
Date date = new Date();
cal.setTime(date);
jdbctemplate.update("...", cal);

Without explicit type information, Spring maps the Calendar parameter internally to:

ps.setTimestamp(paramIndex, new java.sql.Timestamp(cal.getTime().getTime()));

The correct answer is to specify the exact type to Spring:

jdbctemplate.update("...", new SqlParameterValue(java.sql.Types.TIMESTAMP, cal));

Spring will then map this to the correct JDBC access:

ps.setTimestamp(paramIndex, new java.sql.Timestamp(cal.getTime().getTime()), cal);