Work at SourceForge, help us to make it a better place! We have an immediate need for a Support Technician in our San Francisco or Denver office.

Close

#53 Add property to force timezone for dates

open
nobody
None
5
2012-08-15
2007-12-11
Brian Schlining
No

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!!

Discussion


Anonymous


Cancel   Add attachments