Peter Marklund's Home

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
    adjust(:up)
  end

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

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

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

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!