MySQL hates Timezones

We've started using the TzTime plugin which leverages the Tzinfo gem. It's pretty good. I've adjusted it slightly for my own preferences, but it works pretty well out of the box.

Unfortunately, we ran into some odd issues. Rails suddenly started to find records that it shouldn't. Eg if we were looking for all Event objects that occurred between two given dates, (and expecting to find none) suddenly we were finding one. Which played havoc with our tests :P

The occurred_at date on the Event object was clearly outside of the date-range we were looking at. We even tested to make sure the timezone wasn't stuffing it around and moving it into the date-window.

Eventually we discovered that the to_s(:db) command was printing the date out in a weird format using a T: '2008-07-10T00:50:23+00:00'. This is quite different from the standard MySQL format, which would have it as just: '2008-07-10 00:50:23'

MySQL needs to be set up properly to accept the "T-format" for DateTime objects and ours hasn't been. However, in our case it's much easier to simply extend Rails than to go through the required infrastructure politics to get global database settings changed (which will affect all the other projects in the business).

So I just updated the way that the to_s(:db) format works to always use the 'Y-M-D H:M:S' format as below.

ActiveSupport::CoreExtensions::Time::Conversions::DATE_FORMATS.merge!(:db => '%Y-%m-%d %H:%M:%S')
ActiveSupport::CoreExtensions::Date::Conversions::DATE_FORMATS.merge!(:db => '%Y-%m-%d %H:%M:%S')

This seems to work just fine. It means that we'll need to update this if we ever change away from MySQL - but MySQL seems to be hard-coded into this organisation and seems pretty unlikely.

Note: when I first put this code into config/environment.rb it worked fine until I ran the tests - at which point I immediately got an error: uninitialized constant ActiveSupport. This went away the moment I moved the code below the Rails.initializer code (ie underneath the line that says: Include your application configuration below).