Tuesday, November 08, 2005

GMT dates for Mysql (MST7MDT) via JDBC, success

Finally able to store MST dates as GMT time in the database. Had to load the timezone tables first, /usr/local/mysql/bin/mysql_tzinfo_to_sql /usr/share/zoneinfo/ | /usr/local/mysql/bin/mysql -u root -p mysql. Here's the setup:

mysql 4.1.3-beta-standard

Connector/J:3.1.11-bin OR 3.2.0-alpha-bin

url:
jdbc:mysql://localhost:3306/unity?useUnicode=true&characterEncoding=utf8&useTimezone=true&serverTimezone=MST7MDT

mysql executable: /usr/local/mysql/bin/mysqld_safe --user=root --default-time-zone=MST7MDT &

Prepared statement (Hibernate)code:

public void nullSafeSet(PreparedStatement st, Object value, int index) throws HibernateException, SQLException{
Timestamp timestamp;
if(value instanceof Timestamp){
timestamp = (Timestamp) value;
}
else{
timestamp = (Timestamp) new LocalizedTimestamp();
}

Calendar gmtCal = Calendar.getInstance(GMT);
//where GMT = TimeZone.getTimeZone("GMT-00:00");

st.setTimestamp(index, timestamp,gmtCal);
}


The catch is that this code will be called on all of our TIMESTAMP types, since we're overriding the existing Hibernate TIMESTAMP type (as LocalizedTimestamp). However, this could be easily resolved by the generated DAOs setting some flag on the LocalizedTimestamp object. As it is, some of us are of the mindset that all the timestamps we store should be GMT, so this still works.

-----------
Update
The alternative, producing the same result, is as follows:
/usr/local/mysql/bin/mysqld_safe --user=root --default-time-zone=UTC &

jdbc:mysql://localhost:3306/unity?useUnicode=true&characterEncoding=utf8&useTimezone=true&serverTimezone=UTC

st.setTimestamp(index, timestamp);

No comments: