Reading and Writing UTC Timestamps to DB with Hibernate

Reading and writing UTC timestamps to a database when the default timezone may change. E.g., this might happen in an application server if an application running in the same JRE changes the default timezone as follows:

 
TimeZone.setDefault(TimeZone.getTimeZone("Europe/Zurich"));

The easiest solution to solve this problem I know is to create an own mapping type extending the standard Hibernate timestamp type org.hibernate.type.TimestampType:

 
package ch.meteoswiss.commons.hibernate;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Calendar;
import java.util.SimpleTimeZone;

/**
 * <tt>Timestamp</tt>: A type that maps an SQL TIMESTAMP to a Java
 * java.util.Date or java.sql.Timestamp using UTC time zone.
 */
public class UTCTimestampType extends org.hibernate.type.TimestampType {
     
    @Override
    public Object get(ResultSet rs, String name) throws SQLException {
        return rs.getTimestamp(name, createUTCCalendar());
    } 

   /**
    * Creates UTC calendar. DO NOT USE a static calendar instance.
    * This may lead to concurrency problems with (at least) the Oracle DB
    * driver!
    * @return Calendar with UTC time zone.
    */
    private static Calendar createUTCCalendar() {
        final Calendar c = Calendar.getInstance();
        c.setTimeZone(new SimpleTimeZone(0, "UTC"));
        return c;
    }

    @Override
    public void set(PreparedStatement st, Object value, int index) 
        throws SQLException {
            
        Timestamp ts;
        if (value instanceof Timestamp) {
            ts = (Timestamp) value;
        } else {
            ts = new Timestamp(((java.util.Date)value).getTime());
        }
        st.setTimestamp(index, ts, createUTCCalendar());
    }

}

Use it as follows with Java annotations (you could use the type class also in a XML configuration file):

 
import java.util.Date;

import org.hibernate.annotations.Type;
@Entity
@Table(name="...")
public class Data {
   
    private Date receptionTimeDt;

    @Type(type="ch.meteoswiss.commons.hibernate.UTCTimestampType")
    @Column(name="RECEPTION_TIME_DT", nullable=false)
    public Date getReceptionTimeDt() {
            return receptionTimeDt;
    }
}

Of course, this mapping class only works with Hibernate and is not standard JPA.

Original post: http://peter-on-java.blogspot.com/2012/04/reading-and-writing-utc-timestamps-to.html

Tags orm