Wednesday, November 09, 2005

GMT time and my headache

So say you care about a given date, like today, 9 Nov. It means so much to you that you want to store it in the database. Turns out if you're using Java, then what you're really storing is the GMT millis of the date and the database is storing that date in the same format. In other words, regardless of the time zone in which you reside, or where the database resides, 9 Nov GMT is 9 Nov GMT.

Regardless of my posts earlier about storing GMT dates in the database, I'm not so conviced that we *weren't* doing that in the first place. Fact is, using the Calendar in the PreparedStatement really only hoses with the millis being stored and my verification that "the right timestamp" was being stored was purely based on the String representation. In all actuality, if millis going in == millis coming out, then the date that is being stored at the moment indicated by the millis is very much indeed being stored in GTM.

But what about the settings for getting Oracle (alter database time_zone=xyz) or Mysql (mysqld_safe --default-time-zone=xyz)? I dunno what bearing that actually has. What I do know is that regardless of the time zone that Mysql is initiaiized with, the String representation of any date always appears as MST. For example, I have a date representing today (9 Nov 00:00:00.000 GMT). I persist this via JDBC and then immediately request back the value of the timestamp. Both sets of millis are identical. However, the date shown from toString() shows 8 Nov 17:00:00.000 since Java knows that JVM's timezone is MST7MDT. Likewise, if I access that same record and look at the date through the mysql command console, I see the same date!

What does this mean? I can explain the Java representation since it's using the timezone of my box (and Bryan and I tested it when he set his Apple to Tokyo time and we verified millis were correct, but String was appropriate for the Tokyo timezone), but I cannot explain the mysql console. Furthermore, why can't I just see millis? Something is translating those millis into MST7MDT. Still need to test this on Oracle. But, for the time being, I'm quite confused about the notion of storing timestamps in GMT when it appears that the Oracle/Mysql are doing exactly that anyway (as indicated by my tests, which I should post).

No comments: