Convert old MySQL datetime values from system time zone to UTC (helpful for rails 2.3 apps upgrading to rails 3.x)

model_record_updates.rake

namespace :model_record_updates do
  desc 'Convert all datetimes to UTC values'
  task :convert_datetime_to_utc => :environment do
    puts "Environment: #{Rails.env}\n\n\n"

    #
    # This should be run only once, since it will adjust the database values every time it is run!
    #

    adjust_by   = '+05:00' # How much to alter datetime value by. This is case-by-case, and mine was CST!
    ar_database = ActiveRecord::Base.connection.current_database
    puts "Gathering all tables that contain a datetime column"
    tables = ActiveRecord::Base.connection.execute("
      SELECT DISTINCT table_name
      FROM `information_schema`.columns
      WHERE
        data_type = 'datetime' AND
        table_schema = '#{ar_database}'
    ")

    puts "Looping #{tables.count} tables to convert datetime to UTC"
    tables.each do |table|
      table_name = table.first
      columns = ActiveRecord::Base.connection.execute("
        SELECT column_name
        FROM `information_schema`.columns
        WHERE
          data_type = 'datetime' AND
          table_schema = '#{ar_database}' AND
          table_name = '#{table_name}'
      ")
      columns = columns.collect{ |column| "#{column[0]} = CONVERT_TZ(#{column[0]}, '+00:00', '#{adjust_by}')" }.flatten.join(', ')

      puts "\tConverting datetime columns for #{ar_database}.#{table_name}"
      ActiveRecord::Base.connection.execute("UPDATE #{table_name} SET #{columns}")
    end
  end
end