Tuesday, December 16, 2008

MySQL dates that work across multiple time-zones

Wouldn't it be nice if you could store date information in your database that would be correct regardless of your server and client time-zones (that may themselves change over time)?

One way to do this is to store all date information relative to the GMT time-zone regardless of the server and client time-zones. Whenever a client interacts with a date you simply convert it from GMT to the client's specific time-zone and back as needed.

Well, after 12 hours of investigation, I finally got this to work ;) Here's how:

  1. Configure the server to use the GMT time-zone by inserting default-time-zone=UTC (case sensitive) in my.cnf
  2. Set the useLegacyDatetimeCode MySQL driver parameter to false. This fixes this bug: http://bugs.mysql.com/bug.php?id=15604
  3. When reading/writing dates from/to the database always use UTC dates
That's it.

No comments: