Read more

How Rails and MySQL are handling time zones

Tobias Kraze
September 03, 2010Software engineer at makandra GmbH

The information in this card is not entirely correct for recent versions of Rails. Please see our card on time zones in Rails 5+ for up-to-date information.

When working with times and dates in Rails applications, you need to deal with the following problem:

  • In Rails, Time objects have a time zone. You can get the zone name by doing time_object.zone.
  • This zone is considered when doing time calculations, e.g. 10 AM CEST minus 8 AM UTC is zero.
  • A datetime in MySQL does not have a zone. It just stores the literal string "2010-05-01 12:00:00".
  • That means that Rails must make assumptions about timestamps loaded from and written to MySQL.
Illustration money motivation

Opscomplete powered by makandra brand

Save money by migrating from AWS to our fully managed hosting in Germany.

  • Trusted by over 100 customers
  • Ready to use with Ruby, Node.js, PHP
  • Proactive management by operations experts
Read more Show archive.org snapshot

Rails has two completely different modes of dealing with this.

We also prefer to use mode 1.

Mode 1: Automatic time zone conversion is disabled

  • This is the default when you create a new Rails 2 application, but not when you create a Rails 3 application or newer.
  • You should be using this if you can get away with it, because option 2 is a lot more pain.
  • In this mode Rails assumes that your application lives in the same time zone as your server's local zone settings.
  • In this mode ActiveRecord will not try to convert times coming out of Time.now or Time.parse when saving or loading a record.
  • Find out the locale zone of your server by doing date on the shell. E.g. our main application servers are running on Berlin (CEST) time, but this will differ on other machines.
  • Rails also assumes that datetime fields in MySQL represent times in your local zone. E.g. Post.last.created_at.zone will be "CEST" if your server is running in "CEST".
  • This mode will work for you if your application should display times in the server's local zone.
  • This mode will not work for you if your server is running on UTC and you want to display times for German users.
  • When "turning off" time zones, one must not use Time.current any more, since there's always a time zone (UTC). Actually, you cannot "turn off" time zones, just try to ignore them.

In order to disable time zone conversion in Rails 2, open config/environment.rb and comment or remove the config.time_zone line:

# config.time_zone = 'Berlin'

In order to disable time zone conversion in Rails 3 or newer, open config/application.rb and add the following:

config.active_record.default_timezone = :local
config.active_record.time_zone_aware_attributes = false    

keep the time zone set to your local time zone (i.e. "Berlin" for us).

Mode 2: Automatic time zone conversion is enabled

  • This is the default when you create a new Rails 3 application, but not when you create a Rails 2 application.
  • In this mode Rails keeps all Time object in the configured zone, but stores them in MySQL as UTC times (regardless of the time zone you chose).
  • E.g. when an ActiveRecord has a Time field set to "2010-09-03 12:00:00 CEST", it will be stored in MySQL as "2010-09-03 10:00:00" and converted back when loading the record.
  • In this mode you can switch time zones (even per request) without any problems, all conversions are handled.
  • There is an important caveat that the time returned by Time.now or Time.parse will not be converted automatically when it is used for MySQL queries (because it is not a TimeWithZone). You need to use Time.current, Date.current, DateTime.current, Time.zone.parse, etc. everywhere. You should read this guide Show archive.org snapshot for a pretty exhaustive list of all the things you need to pay attention to.
  • If you require some code that does Time.now or Time.parse for you (e.g. a gem), you need to monkey patch this or be out of luck.
  • For a list of all available time zones, run rake time:zones:all. The zone string for Germany is "Berlin".
  • Take care when you set this configuration option at a point where you already have records in the database. If your server was not running with a UTC time zone, all your timestamps will be off by some hours because of the changed assumption Rails is making about those timestamps now. In this case, you have to correct all times in the database manually.
  • config.time_zone is the default zone for a new application process. If your application should support user-configurable time zone, you can just set Time.zone = "..." in a before_action.

In order to enable time zone conversion in either Rails 2 or Rails 3, open config/environment.rb and define your time zone:

config.time_zone = 'Berlin'

Again, please read the working with time zones in Ruby on Rails Show archive.org snapshot from Elabs Show archive.org snapshot before you do anything in an application with time zones enabled.

Also see

Tobias Kraze
September 03, 2010Software engineer at makandra GmbH
Posted by Tobias Kraze to makandra dev (2010-09-03 12:27)