Skip to content

Instantly share code, notes, and snippets.

@yellow5

yellow5/model_record_updates.rake

Created May 12, 2012 03:12
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save yellow5/2663883 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save yellow5/2663883 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. yellow5 revised this gist May 12, 2012. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion model_record_updates.rake
    View file
    Original file line number Diff line number Diff line change
    @@ -7,7 +7,7 @@ namespace :model_record_updates do
    # 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. case-by-case, mine was CST!
    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("
  2. yellow5 created this gist May 12, 2012.
    38 changes: 38 additions & 0 deletions model_record_updates.rake
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,38 @@
    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. case-by-case, 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