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.
Before we dive into Ruby on Rails, we need to start at the foundation: how MySQL stores and retrieves dates and times.
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.
- The MySQL system variable
time_zoneis 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_zonefor that directive instead.
system_time_zoneis 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.
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"
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:
- Each Ruby thread keeps track of the time zone in
Time.zone. Just like MySQL, Ruby defaults to the system time zone.
- We can change the time zone of the current Ruby thread via
Time.zone = 'Eastern Time (US & Canada)'thanks to ActiveSupport.
- 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 (
Time.nowwill 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.currentitself actually calls
Time.parseshares 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.
- 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.
- 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.
config.time_zonesetting. 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:
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:
- It’s an unusual MySQL feature that we would have to wrap our brain’s around and translate between MySQL and Ruby on Rails.
- It requires up-to-date TZInfo to be regularly loaded into our database server.
- 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
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:
- We’re going to be regularly updating the time zone table by time zone name anyway.
- When using Rails’
#in_time_zonemethod, 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.
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.
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.
3Hat tip to Nathan Herald for the around filter idea.
3 thoughts on “Time Zones, Ruby on Rails, and MySQL”
For those looking for more, Ryan Bates just released a RailsCasts Pro screencast on the topic: http://railscasts.com/episodes/106-time-zones-revised
Hi Ian, thanks for the guide–it’s relevant. Just wanted to note that there are more than 4 time zones for the US.
Thanks. Just inserted “contiguous.” Please don’t bring up Arizona. :D