Wednesday, September 19, 2007

Saving datetime into database, what timezone is it in?

Moments ago I posted a review on and returned to the product's page, it shows the review was posted 7 hours ago. It seems like Amazon is having a timezone conversion issue.

I am not trying to pick on Amazon. The funny part is that right this afternoon I was asked by a coworker about what timezone a datetime column of our database table is in. Then we had a discussion about how to handle timezones.

Timezone in databases is a delicate issue, and a tricky one to fully understand. Conversion is one issue. Others issues include moving data between database servers running in different timezones, moving data between different database dialects, interpret archived data, etc.

In the past I tried a couple approaches besides blindly saving the Date object through JDBC regardless of the timezone information. In one application I converted the datetime property into UTC time before saving it. In another application I converted time into milliseconds and saved it into database as long number.

I don't have a clear understanding of the relationship among the JVM's timezone, the Date object's timezone, the database server's timezone, the Timestamp's timezone in JDBC result set, and the Datetime column's timezone is MS SQL Studio's result set.

I have the full intension to study this topic in depth and report back.

Stay tuned.

No comments: