Learn how easy it is to sync an existing GitHub or Google Code repo to a SourceForge project! See Demo


#53 Add property to force timezone for dates

Brian Schlining

I like to write all dates to the database as UTC (i.e. GMT) time, then read them back assuming that the date was stored as GMT. Unfortunately, most databases don't store timezone information with dates/timestamps/datetimes. The default behavior of most JDBC drivers is to write the data as the applications local time and read back the data as app local time. If you have applications in different timezones reading and writing dates, your dates tend to walk in value and they quickly lose any meaning. This is exacerbated by many Java object-mapping frameworks that make it difficult to deal with timezones.

The MySQL JDBC driver (mysql-connector-java) has some handy properties that can be specified in the JDBC URL that forces the dates to be written to a particular timezone on the server and another that forces dates to be converted correctly. The properties descriptions can be found at http://dev.mysql.com/doc/refman/5.0/en/connector-j-reference-configuration-properties.html. The 2 of interest are:

serverTimezone -> Override detection/mapping of timezone. Used when timezone from server doesn't map to Java timezone.

useTimezone -> Convert time/date types between client and server timezones (true/false, defaults to 'false')?

A URL on MySQL like "jdbc:mysql://someserver.com/MYDB?serverTimezone=UTC&useTimezone=true", will force the JDBC driver to store dates in the database as GMT but correctly convert them to the local timezone when read by an application.

It sure would be great if we could do the same thing with jTDS!!



Cancel   Add attachments