Time Zones, Ruby on Rails, and MySQL

A few months ago, I had the privilege of taking a Rails 3 application national. One of the requirements for doing so was ensuring that it supported each of the four time zones in the contiguous United States of America. Just switching the application from EST to UTC and using JavaScript to render times to local time would have been ideal. However, there were several API, native application, and legacy code constraints that prevented us from using that trick. We needed the entire request/response cycle to be in the local time zone.

While doing my research, I was unable to find a single source of reference material that thoroughly explained how a standard Ruby on Rails stack dealt with time zones and how an engineer could leverage it. This post is meant to serve those who find themselves in a similar position.

MySQL

The Basics

Before we dive into Ruby on Rails, we need to start at the foundation: how MySQL stores and retrieves dates and times.

1. DATE, TIME, and other date and time column types are time zone agnostic.¹ Even worse, we can store invalid dates and times in them. Think of them as specially-formatted string types.
2. The MySQL system variable time_zone is used internally by functions like NOW() to tell MySQL what time zone it’s in so it knows what hour to output. The variable defaults to the value SYSTEM, but it can be set to any specific time zone. When its value is SYSTEM, any functions that rely on it fall back to system_time_zone for that directive instead.
3. system_time_zone is the time zone of the database’s operating system. If our ops team knows what it’s doing, all our servers are set to UTC. However, we can’t assume that’s true or will always be true. Besides, each developer on our team could have his or her laptop set to a different local time zone.

Let’s Standardize

Rails itself doesn’t require any sort of time zone setting in MySQL because it just looks at the data like it’s a string. However, we want to stay sane when comparing data or inspecting the results of query execution in development versus production. It would be good to standardize.

To get every system on the same page, we go to each machine (laptops and servers) and set the MySQL time zone in /etc/my.cnf² to UTC:

  [mysqld]
  default-time-zone = '+00:00'

This requires the MySQL server to be restarted in each of those environments. We can double check our work after restarting by running the following query:

  show variables like "%time_zone"

Note that system_time_zone will be the zone of the operating system, but time_zone is going to be +00:00 regardless. If we run SELECT NOW(); on any machine with this configuration, we’ll get the same, consistent time in UTC—of course, offset by any delay we introduce when switching between each MySQL console.

Implications and Further Reading

The only downside to all this is that we have to start thinking in UTC when developing. Well, time to level-up, Mr. Engineer.

If you want more information on MySQL’s handling of dates, times, and zones, let me recommend the MySQL documentation as well as Sheeri Cabral’s video Time Zones in MySQL.

Ruby (sprinkled with ActiveSupport)

Ruby itself does not have time zone support. Everything is in system time. Rails strengthens Ruby’s time related classes with time zones (ActiveSupport::TimeWithZone). The result of this combo is:

1. Each Ruby thread keeps track of the time zone in Time.zone. Just like MySQL, Ruby defaults to the system time zone.
2. We can change the time zone of the current Ruby thread via Time.zone = 'Eastern Time (US & Canada)' thanks to ActiveSupport.
3. We can shift a Time instance to another time zone via #in_time_zone('Pacific Time (US & Canada)'). We omit the parameter if we want to convert a Time instance to the time zone of the current Ruby thread (#in_time_zone).
4. Time.now will always return the current time in the system time zone regardless of the time zone setting (bleh). So, instead of using Time.now, we have to use Time.current. (Time.current itself actually calls Time.now.in_time_zone.)
5. Time.parse shares the same fate. Instead, we use Time.parse('2012-1-1').in_time_zone('Pacific Time (US & Canada)').

Rails

Rails 3 gets with the program and acknowledges that any real app is going to want to run in UTC.

ActiveRecord

1. ActiveRecord has a time zone setting. Any dates or times that we assign to a model instance get auto-converted to that zone when being stored to the database, and back again when read from it. Smart.
2. The standard ActiveRecord config defaults to :local, which is the system time zone. However the ActiveRecord railtie sets it to :utc. A tad confusing, but the result in our Rails app is UTC out of the box.

ActionPack

1. application.rb has a config.time_zone setting. This is the time zone of any request/response thread and does not affect what’s stored through ActiveRecord. The default is UTC, but we can set it to something else if we want.

What to Do

All examples I’ve seen online recommend setting the request/response thread’s time zone in a before_filter via something like Time.zone = current_user.time_zone. The problem with this approach is that we need to make sure every request goes through that filter and that we recover from any exceptions that happen during that request/response cycle to set the time zone back to UTC appropriately. We could accomplish this by using an around_filter with an ensure so that subsequent requests start from a good time zone baseline of UTC³:

  around_filter :reset_timezone_to_utc
  before_filter :set_timezone_to_user

  def reset_timezone_to_utc
    yield
  ensure
    Time.zone = Time.zone_default
  end

  def set_timezone_to_user
    Time.zone = current_user.time_zone
  end

Although this looks good on a blog, making all requests go through the same time zone logic in a larger Rails app is unrealistic—believe me. Not every action in your application is going to be user-time-zone-centric. Instead, I recommend running everything in UTC and converting to the appropriate time zone each place you need to display a date or time in a view or mailer. For example, in one place we might have:

  some_order.created_at.in_time_zone(some_order.purchaser.time_zone)

and in a different view of the same record somewhere else in our application, we may want:

  some_order.created_at.in_time_zone(some_order.seller.time_zone)

Queries

If any of our queries, whether MySQL (or Sphinx), need to utilize time zones and their offsets, we’re going to need a table of time zones in our database. MySQL has a way to load TZInfo into special tables. However, there are a few problems with this approach:

1. It’s an unusual MySQL feature that we would have to wrap our brain’s around and translate between MySQL and Ruby on Rails.
2. It requires up-to-date TZInfo to be regularly loaded into our database server.
3. It requires calculations of offsets for Daylight Savings at query execution time.

There is a more Railsy way of accomplishing the same goal: build our own time zone table, and fill it with time zone info from ActiveSupport::TimeZone.all.

class AddTimezones < ActiveRecord::Migration
  def self.up
    create_table :timezones do |t|
      t.string :name, :null => false
      t.string :offset, :null => false
      t.timestamps
    end

    add_index :timezones, :name, :unique => true
  end

  def self.down
    drop_table :timezones
  end
end

We add a time zone column to each model that needs it.

class AddTimezoneToSellers < ActiveRecord::Migration
  def self.up
    add_column :sellers, :timezone, :string, :null => false
  end

  def self.down
    remove_column :sellers, :timezone
  end
end

We associate records by time zone name and not ID because:

1. We’re going to be regularly updating the time zone table by time zone name anyway.
2. When using Rails’ #in_time_zone method, we just need to pass the time zone name in. There’s no point to joining on another table just to get a string back.

And, finally, we’re going to keep the time zone table up-to-date each morning.

class Timezone < ActiveRecord::Base
  # This model is really just for filling/updating the timezones table for MySQL and Sphinx
  # I tried removing the ID column and making `name` the primary key, but ran into an obscure Rails `schema.rb` generation bug
  def self.reload_tzinfo_into_database
    ActiveSupport::TimeZone.all.map do |tz|
      tz_record = Timezone.find_or_initialize_by_name(:name => tz.name)
      tz_record.offset = Time.now.in_time_zone(tz.name).formatted_offset
      tz_record.save!
    end
  end
end

With the following rake task:

namespace :timezones do
  desc 'Creates and/or updates timezones records with time zones and their current UTC formatted offsets'
  task :reload => :environment do
    ActiveSupport::TimeZone.all.map do |tz|
      tz_record = Timezone.find_or_initialize_by_name(:name => tz.name)
      tz_record.offset = Time.now.in_time_zone(tz.name).formatted_offset
      tz_record.save!
    end
  end
end

And then we set up the rake task as a daily cron job on our server.

The End

Setting up a Ruby on Rails application and MySQL (and Sphinx) to support multiple times zones is quite the journey. I’ve tried my best to take good notes, but if you spot something funny, feel free to leave a comment. Just don’t be a troll. Thanks.

Footnotes

¹Except TIMESTAMP, which is stored as UTC and shifted when queried. TIMESTAMP is a MySQL-proprietary column type.
²my.cnf may be somewhere else if you have a different installation of MySQL than I do.
³Hat tip to Nathan Herald for the around filter idea.