Time Zones, Ruby on Rails, and MySQL

Ad: Need an Application Security Audit or Training for your Engineering Team?

If you are looking for a firm to do an application security audit for your web or mobile application or to train your software engineers on writing secure code, I highly recommend nVisium Security. (Tell them Ian Lotinsky sent you.)

The Blog Post

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.


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:

  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 3 gets with the program and acknowledges that any real app is going to want to run in UTC.


  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.


  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
    Time.zone = Time.zone_default

  def set_timezone_to_user
    Time.zone = current_user.time_zone

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:


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



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

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

  def self.down
    drop_table :timezones

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

  def self.down
    remove_column :sellers, :timezone

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

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

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.


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.

Gemfiles and GitHub Enterprise

It took me about 30 minutes to figure out how to point a Gemfile to a GitHub Enterprise repository, in a GitHub organization, in a branch. Here’s the solution I whipped-up:

gem 'mygem', :git => "http://username:password@ourgithub.ourdomain.com/organization/mygem.git", :branch => "bugfix/mybranch"

However, do not check this in to your repo. Your username/password is in plain text.

If anyone has a better suggestion, please post it in the comments. Thanks.

Team Debt

I’m currently having a blast leading the technical team behind the LivingSocial Takeout & Delivery web site. One of the challenges of a growing team is maintaining appropriate amounts of communication. You want everyone to know everything that’s important, but not everything. Otherwise, you end up being a case study in The Mythical Man Month.

Although our team did not follow this plan when it was ramping-up, hindsight reveals the need for a team debt management strategy as it grows. After mulling over it for awhile, I’m fairly sure that if I lead a new team in the future, we will follow this path:

First engineer to join the team

  1. Sets-up the source code repository
  2. Writes a starter project README
  3. Provisions the application and team notification email addresses
  4. Wires-up application notification email(s)

Second engineer

  1. Sets-up the continuous integration (CI) server
  2. Provisions the CI notification email address(es)
  3. Wires-up CI notification emails

Third engineer

  1. Sets-up the team’s Campfire
  2. Wires-up commit and deployment notifications (Campfire and/or email)

Fourth engineer

  1. Sets-up a scrubbed production database dump that engineers can use for local development

What tech team debt tools do you typically employ, and when do you employ them?

My Startup Lesson Learned

A few years ago, I quit my job to build a bootstrapped startup called SandwichBoard–a content management system for restaurants. Patrick Joyce and I put $5,000 into the business and made a leap of faith to live off savings and occasional consulting gigs.

After we got the sales routine down, we were growing at a steady pace, but not early or fast enough for our personal lives. We both really wanted to marry our girlfriends. We needed money, and ultimately had to ditch the bootstrapping salaries. I was elated to be marrying the woman I had been waiting for my whole life, but throwing-in the startup towel felt like a huge loss. It was something I had been working towards for a long time, but giving it up to marry my wife was the best decision I ever made.

Now that I’ve had time to reflect on what went well and what didn’t, I have my own list of lessons learned–things I would do again or make sure never to go near when building a business or product. But you’ve already seen that sort of list before. Instead of dumping a bunch of do’s and don’ts on people, I would rather leave them with an encouragement–one that will give someone else the courage to take that leap of faith.

My lesson learned: even though the startup plane may come to a fiery end, the pilots have ejection seats.

Our efforts were not wasted. While building SandwichBoard, we became proficient in Ruby on Rails, resulting in a great product and more than two significant open source contributions. This helped land Patrick a position at a company called Hungry Machine and me a job at Razoo. Taking that risk propelled us from working typical IT jobs into the startup world. I’ve had fun just about every day since I took that leap of faith, become a better engineer at an accelerated rate, been able to work with incredible people, and turned that savings loss into a short-term investment.

Patrick’s employer renamed itself LivingSocial, and his first major responsibility was to develop a small site called LivingSocial Daily Deals. (Maybe you’ve heard of it.) I rejoined Patrick this July and am leading the technical team responsible for building LivingSocial’s latest local-commerce product: Takeout & Delivery–restaurant takeout and delivery ordering (currently only in Washington, DC). It’s a fitting end to the SandwichBoard story.

Results may vary, but I still think it’s worth a try. Give it a go; jump off that cliff!

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?

JavaScript Style Sheets

One of the privileges I have experienced in my professional life is having worked with Jeremy Keith of Clearleft. I learned a lot from the author and web developer from our interactions and by studying his code.

Jeremy is a fan of graceful degradation and ensures that a site will function just fine without JavaScript browser support. However, instead of mingling JS and non-JS styles together, he employs a particularly clever technique. He puts the standard styles in the standard CSS file. But just as his sites provide print and mobile stylesheets for printers and mobile devices, he also provides one for JS-enabled browsers. This has two nice side-effects: (1) better CSS organization and (2) CSS that is applied immediately, not after JS has had an effect on the DOM.

One example is a thumbnails carousel he designed. If the browser does not have JS support, the thumbnails are arranged in a grid. If the client does support JS, he wraps-up the thumbnails in a single row inside a horizontally-scrolling carousel, so they can be browsed left to right. The carousel presentation of the thumbnails requires a different set of CSS rules, which override the vanilla styles.

Here’s how he pulls off the wiring-up of a JavaScript CSS style sheet:

  <script type="text/javascript">document.write('<link rel="stylesheet" href="/stylesheets/javascript.css" media="screen,projection">');</script>

Any site I’m involved in will be following suit.

Thanks, Jeremy!