Category: Ruby on Rails

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.1 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.cnf2 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 UTC3:

  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

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

strip_attributes, Rails 3, shoulda 2.11 hack

I have never understood why Rails doesn’t strip attributes by default. I know at least one person who tried committing it to core, only to have it rejected. I always end up installing the strip_attributes plugin.

I’m ramping-up a new Rails 3 project, with Shoulda 2.11. I installed strip_attributes. It works, but the strip_attributes Shoulda macros don’t work anymore. I could take the route of upgrading the plugin, but then I would “have to” also refactor the Shoulda macros since macros have been ditched for matchers. Then I’d have to re-write the tests. At that point, I might as well make it a gem and add that feature I’ve always wanted. But, I’m just not ready for that sort of commitment right now (#gtd_maybe_someday).

So, here’s a short line to add to the end of strip_attributes/init.rb to get the old strip_attributes Shoulda macros working again:

require File.expand_path(File.join(File.dirname(__FILE__), 'shoulda_macros', 'macros.rb')) if Rails.env.test?

Rails path and url helpers

Sometimes I forget the simple differences between Rail’s helpers. This mini post is so I don’t forget.

*_path
Generates relative URLs: /users
Used in views by link_to, form_for, etc. (per DHH)
The browser maps relative URLs to absolute URLs based on the current page’s protocol and host (/users on the page http://domain.com/new translates to http://domain.com/users)

*_url
Generates absolute URLs: http://domain.com/users
Used in controllers by redirect_to (per DHH) because RFC 2616 states that redirects must be absolute URLS. This is true, however, modern browsers can handle relative URL redirects now too

assert_raise in Rails Integration Tests

Today I wanted to assert that a POST from an integration test resulted in an exception being raised under certain conditions. I had done this bunch of times in unit and controller tests, so I just ported that methodology to my integration test. Here is a contrived example of how my original test read:

  def test_that_exception_is_raised
    exception = assert_raise(RuntimeError) {
      post '/comments', {:comment => {:message => 'I hate you!'}}
    }
    assert_equal 'Say something nicer please. Thank you.', exception.message
  end

The problem was that the assertions were failing because <RuntimeError> exception expected but none was thrown. But I clearly saw in my test.log that the exception was thrown:

  RuntimeError (Say something nicer please. Thank you.):
    app/controllers/comments_controller.rb:789:in `create'
    test/integration/comments_test.rb:456:in `__bind_1278520981_664592'
    /ruby/lib/ruby/8.8/test/unit/assertions.rb:123:in `assert_raise'

I thought about about the differences between functional and integration tests in Rails, and then realized that although the exception was thrown, that’s not what is returned to the browser. Instead, the browser is sent an error page. Then it became glaringly-obvious, how to write my assertion:

  def test_that_exception_is_raised
    post '/comments', {:comment => {:message => 'I hate you!'}}
    assert_response :error, 'Say something nicer please. Thank you.'
  end

I still feel like a newbie sometimes.

Ruby include and extend

My employer, Razoo, treated its developers to RailsConf 2010 this year. Very late the second night of the conference (the first night I was there–we skipped the tutorials), I saw Yehuda Katz give an impromptu Birds of a Feather talk about upcoming changes in Rails. It was good, but the thing I remember most clearly was his explanation of include and extend in Ruby. He let code do the explaining:

# a module with a method
module Says
  def hello
    puts 'hello'
  end
end

# a class we want to have the method (not an instance; the class)
class Person
end

# (our end goal)
class Person
  def self.hello
    puts 'hello'
  end
end

# there are two ways to achieve this
# using extend
class Person
  extend Says
end

# or using include
class Person
  class << self
    include Says
  end
end

So, in short, include just includes the module’s methods as instance methods and extend includes the module’s methods as class methods. I always do better with examples than reading technical documentation!