Programming Thoughts
Hibernate
SQL Server Time Zones

The situation

A mutli-service architecture of Tomcat servers in Amazon Web Service as tier 1, JBoss servers on AWS as tier 2, MySQL databases on AWS and local SQL Sever 2000 databases as tier 3. System clocks and JVMs on the AWS-based servers are set to UTC time zone, except for the JVMs of the Tomcat server. The SQL Server 2000 databases are set to London time. There's also an IVR system that uses the SQL Server databases.

Timestamps read from and written to the SQL Server 2000 database would often be off by one hour forward or back in time.

JDBC driver

Obviously, the SQL Server databases are adjusting for British Summer Time, or rather, the JDBC driver isn't aware the databases are using London time and it's merely a matter of correct configuration. The JDBC configuration is found at The jTDS Project. There is no configuration of time zone and it assumes the database is using the same as the JVM.

Changing the time zone of the SQL Server risks breaking a lot of legacy IVR code. Setting the time zone of the JBoss JVM should work and won't affect the other Java-based and MySQL servers. This was the previous solution. However, changing a server-wide setting for one errant connection is like making my problem everyone's problem and is asking for trouble in the future. Custom code and settings should only affect the problematic database values. As the JDBC settings couldn't be fixed, this meant the next layer, namely reading timestamps from ResultSets and EJB 3.0 Entity Beans using Hibernate 3.

Timestamps from ResultSets

The JDBC driver seemed to parse datetimes without knowing the time zone. A value using 2am BST, which is 1am UTC, would be parsed as 2am UTC. Thus, the local datetime should be extracted from the London time datetime, then inserted into a UTC datetime. This can be done with Java 8 code, such as below.

if (timestamp == null) { return null; } Instant instant = timestamp.toInstant(); LocalDateTime localDateTime = LocalDateTime.ofEpochSecond(instant.getEpochSecond(), instant.getNano(), ZoneOffset.UTC); ZonedDateTime zonedDateTime = ZonedDateTime.of(localDateTime, ZoneId.of("Europe/London")); Date result = Date.from(zonedDateTime.toInstant());

The value to insert into the database is the reverse of this.

if (date == null) { return null; } ZonedDateTime zonedDateTime = ZonedDateTime.ofInstant(date.toInstant(), ZoneId.of("Europe/London")); LocalDateTime localDateTime = zonedDateTime.toLocalDateTime(); Timestamp result = Timestamp.from(localDateTime.toInstant(ZoneOffset.UTC));

Hibernate 3 custom user type

Member fields of EJB 3.0 Entity beans using Hibernate 3 can be adjusted with custom user types, set with the org.hibernate.annotations.Type annotation. The custom user type can use code like the following.

public class TimeZoneShiftDateTimeUserType implements UserType,ParameterizedType {
    private ZoneId timezone = ZoneId.of("Europe/London");
...
    @Override
    public Object nullSafeGet(ResultSet rs, String[] names, Object owner) throws HibernateException, SQLException {
        Timestamp timestamp = rs.getTimestamp(names[0]);
        if (rs.wasNull()) {
            return null;
        }
        Date date = DateUtils.dateFromDBValue(timestamp, timezone);
        return new Date(date.getTime());
    }

    @Override
    public void nullSafeSet(PreparedStatement st, Object value, int index) throws HibernateException, SQLException {
        if (value == null) {
            st.setNull(index, Types.TIMESTAMP);
        } else {
            Date date = (Date) value;
            date = DateUtils.dateToDBValue(date, timezone);
            Timestamp timestamp = new Timestamp(date.getTime());
            st.setTimestamp(index, timestamp);
        }
    }
...
    @Override
    public void setParameterValues(Properties parameters) {
        if (parameters != null && parameters.containsKey("zoneId")) {
            timezone = ZoneId.of(parameters.getProperty("zoneId"));
        }
    }
...
}