Peter Marklund

Peter Marklund's Home


Rails Tip: Migrate Your Database to UTC

UPDATE: Adam Meehan pointed out that my migration didn't work with DST, i.e. different UTC offsets for datetimes at different points of the year. I updated my migration to use a UTC conversion in the database (leading to a variable interval) instead of using a fixed interval adjustment.

If you want to make use of the timezone support in Rails 2.1 and later you'll need to migrate any existing times that you have in your db to UTC. Here is a migration for PostgreSQL I wrote to do that (you'll probably need to adjust it to work on MySQL):

# This migration will work with DST. Because of DST, if you have your datetimes
# spread across the year they will have different offset, i.e. in Stockholm we are UTC+1 usually
# but UTC+2 in the summer.
class MigrateDatabaseToUtc < ActiveRecord::Migration
  COLUMN_TYPES = [:datetime, :timestamp]

  def self.up

  def self.down
  def self.adjust(direction)
    connection = ActiveRecord::Base.connection
    connection.tables.each do |table|
      timestamp_columns = connection.columns(table).select do |column|

      sign = (direction == :down ? "+" : "-")
      update_clause = do |column|
        "#{} = (#{} #{sign} ((#{} AT TIME ZONE 'UTC')-#{}))"
      end.join(", ")

      execute "UPDATE #{table} SET #{update_clause}" unless update_clause.blank?

When I originally wrote this migration I used a fixed interval adjustment (+1 for Stockholm), i.e. the same approach that Simon Harris uses. However, as Adam Meehan points out in the comments this doesn't work with DST so I adjusted to using a AT TIME ZONE 'UTC' conversion instead that will result in a DST dependent interval. Thanks Adam for pointing this out!