SQL TIMESTAMP headache

So, the truth comes out. Oracle and MySQL appear to not hand back TIMESTAMP as millisecond values. After numerous tests (all Java) in multiple timezones we've decided we're not going insane. The databases appear to not deliver TIMESTAMP as milliseconds, but as a String, always. Even from native console applications. There may be milliseconds that reside underneath, but they represent the String and cannot be retrieved. So, no, the TIMESTAMP going is not *always* normalized to UTC (could only get that to be the case when the db box was UTC and the JVM connecting via JDBC was also UTC). We tried multiple scenarios connecting in different timezones and continually found ourselves asking why the date appeared coming back differently in all places if it was really just stored in milliseconds. Perhaps it is, but it's not being given back that way. And the String is transformed by the JVM into the appropriate milliseconds to represent the toString (on the java.util.Date/java.sql.Timestamp) the JVM thinks it should show in your timezone.

At this point the question becomes, "So, how does this work with internationally replicated/distributed databases servicing multiple international JVMs?" It doesn't work, and after several days of testing, we're feeling confident enough to conclude this: the millis are (sometimes) being adjusted by the JDBC drivers to show you what it thinks you expect--so if you're really expecting UTC, then all the boxes' timezones should be GMT/UTC! That's sort of an issue if you plan on using servers in multiple places throughout the world to handle multiple web applications--which is exactly what we cannot assume.

So, the solution is simple:store the milliseconds as a long. Since Java (and most other sane languages) calculates date based upon the Unix Epoch, it makes sense. Furthermore, there's no guess work as to db timezone, connection timezone, driver millisecond manipulation--it's not a concern anymore.

When does the format of a date being storing in the database really matter? Depends on the need for precision, depends on the need of the application and it also depends on the requirement driving it in the first place. When the requirement dictates international interaction, the first thing that should come to mind is to store the date in a common time: GMT. Other consideration should be given to viewers and manipulators, what do they expect to see the time as? Locally or universal? If the answer for all of these questions point to an international need, the above solution is a simplistic and intuitive approach to resolving the design decision.

-------------begin Hibernate UserType-------------

package org.lds.sql;

import java.io.Serializable;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.sql.Types;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Comparator;
import java.util.Date;
import java.util.TimeZone;
import net.sf.hibernate.Hibernate;
import net.sf.hibernate.HibernateException;
import net.sf.hibernate.UserType;
import org.lds.env.utils.DateUtils;


/**
* Custom Hibernate type for precise UTC millisecond persistance and retrieval using a Timestamp.
* All values represent the given time from the Unix Epoch, 1 Jan 1970 00:00:00.000
* See http://en.wikipedia.org/wiki/Unix_epoch for more info.
* Credit: Rob @ "http://www.hibernate.org/100.html
*
*/
public class UtcTimestamp extends Timestamp implements UserType {
/**
* Gets the value of SQL_TYPES
*
* @return the value of SQL_TYPES
*/
public static int[] getSQL_TYPES() {
return UtcTimestamp.SQL_TYPES;
}


/**
* SQL type.
*/
private static final int[] SQL_TYPES = { Types.BIGINT };

/**
* Creates a new UtcTimestamp instance at this moment.
*
*/
public UtcTimestamp(){
super(System.currentTimeMillis());
}

/**
* With the given milliseconds.
*
* @param utcMillis a long value
*/
public UtcTimestamp(long utcMillis){
super(utcMillis);
}

/**
* Make a copy of the Timestamp.
* @see UserType#deepCopy(java.lang.Object)
*/
public Object deepCopy(Object obj) throws HibernateException {
return (obj == null) ? null : new Timestamp(((Timestamp)obj).getTime());
}

/**
* Compare via {@link Object#equals(java.lang.Object)}.
* @see UserType#equals(java.lang.Object, java.lang.Object)
*/
public boolean equals(Object x, Object y) {
return (x == null) ? (y == null) : x.equals(y);
}

/**
* Timestamps are mutable.
* @see net.sf.hibernate.UserType#isMutable()
*/
public boolean isMutable() {
return true;
}

/**
* Return an instance of the Timestamp or null if no value is specified.
* @see net.sf.hibernate.UserType#nullSafeGet(java.sql.ResultSet, java.lang.String[], java.lang.Object)
*/
public Object nullSafeGet(ResultSet rs, String[] columns, Object owner)
throws HibernateException, SQLException {

long value = rs.getLong(columns[0]);
Timestamp timestamp;

if(rs.wasNull()) {
  timestamp = null;
} else {
  timestamp = new UtcTimestamp(value);
}
return timestamp;

}

/**
* Set an instance of the Timestamp into the database field.
* @see net.sf.hibernate.UserType#nullSafeSet(java.sql.PreparedStatement, java.lang.Object, int)
*/
public void nullSafeSet(PreparedStatement statement, Object value, int index)
throws HibernateException, SQLException {

if(value == null) {
  statement.setNull(index, Types.BIGINT);
} else {
  Timestamp timestamp = (Timestamp)value;
  statement.setLong(index, timestamp.getTime());
}
}

/**
* Return the {@link Timestamp} class.
* @see net.sf.hibernate.UserType#returnedClass()
*/
public Class returnedClass() {
return org.lds.sql.UtcTimestamp.class;
}

/**
* Return the supported SQL types.
* @see net.sf.hibernate.UserType#sqlTypes()
*/
public int[] sqlTypes() {
return SQL_TYPES;
}

/**
* All dates return their value in GMT time.
*
* @return a String value
*/
public String toString(){
SimpleDateFormat dateFormat = new SimpleDateFormat(DateUtils.DATE_FORMAT);

dateFormat.setTimeZone(DateUtils.GMT);

return dateFormat.format(this);
}

/**
* Obtain a UtcTimestamp from a Date.
*
* @param date a java.util.Date value
* @return an UtcTimestamp value
*/
public static UtcTimestamp valueOf(java.util.Date date){
UtcTimestamp utcTimestamp = new UtcTimestamp(date.getTime());

return utcTimestamp;

}

}
-------------end Hibernate UserType-------------