Updating MySQL DATETIMEs for Rails 2.1 Time Zones

So, if you haven’t heard, Rails 2.1 was released yesterday, And it comes chock full of time zoney goodness. But wait, don’t run off and enable time zone support in your app without reading this first!

If you checked out Geoff Buesing’s excellent introduction to using the time zone features, you may have noticed this little gotcha near the end of the article:

The new time zone features assume that the database is storing times in UTC, so if you’re currently storing times in the database in a zone other than UTC, you’ll need to migrate existing data to UTC.

If you’re relatively new to Rails development, you probably have a couple of questions.

Am I currently storing my times in UTC?

Probably not. For a standard Rails 2.0 app, environment.rb will contain the following line, commented as you see here:

  # Make Active Record use UTC-base instead of local time
  # config.active_record.default_timezone = :utc

In other words, unless your system’s local time is configured for UTC or you uncommented this line, you were probably storing DATETIME fields like created_at and updated_at in the local system time. If you just enable Rails 2.1 time zone support, all of your old time data will show up incorrectly, because you’ll be adjusting data for your local time zone that was already saved in your local time zone. Now, if you’ve got a huge production Rails site with users all over the world, then you probably are storing times in UTC and using some sort of time zone support already. But then, you also probably aren’t reading this blog.

How do I migrate my existing data to UTC?

If you’re using MySQL, here’s how: CONVERT_TZ.

Standard disclaimer: Always make a backup of your data before doing bulk updates. I am not responsible for data corrupted as a result of trying to follow this guide, yada yada…

Shut down your Rails app and connect to your application’s SQL database using something like MySQL Query Browser. Regardless of whether your MySQL server had its time zone tables populated for name-based time zones, it will understand the two time zones we are going to use, “SYSTEM”, for your server’s time zone, and “+00:00″, or UTC. For each table containing DATETIME columns which are already saved in your system’s local time zone, issue an update to those columns using CONVERT_TZ. For instance, I have a table called “users” with “created_at” and “updated_at” columns, so I will issue:

UPDATE users SET created_at=CONVERT_TZ(created_at,'SYSTEM','+00:00'),
                 updated_at=CONVERT_TZ(updated_at,'SYSTEM','+00:00')

Since there’s no WHERE clause in this query, it will update those two columns in every row in the table. Repeat as necessary. BE CAREFUL! Don’t run these queries against a specific column more than once. If you’re in any way uncomfortable making this change, you should probably have someone else do it.

Once all of your DATETIME columns have been updated, you can safely set up your system’s time zone in environment.rb to enable time zone support:

  # Run "rake -D time" for a list of tasks for finding time zone names.
  # Uncomment to use default local time.
  config.time_zone = 'Eastern Time (US & Canada)'

After starting up your Rails app, all of your time data should continue to show up properly, and new data will be saved in UTC.