What is the correct way to migrate a date column from string to datetime format?

Question

Ruby on Rails

Good afternoon!
There is an object in rails that has a "end time" property (for example, 03/24/2016 at 13:00 (UTC+2) ). It is stored now as a string.
What is the correct way to set up a database migration to change the column format to datetime and process all existing records in the DB?
Here's what I got up to:

class ChangeQueryDateColumn < ActiveRecord::Migration
  def up
    change_column :pages, :query_date, :datetime
  end
  
  def down
    change_column :pages, :query_date, :string
  end
end

This is the migration itself. But the main gag is how to change existing entries?
I wrote a regular expression for this, but I don’t understand where to insert it in migrations:

Page.find(:all).each do |page|
  page.query_date = page.query_date.gsub(/\(.*/, '').to_datetime
  page.save!
end

Can you tell me how to do it right? Thanks in advance for your reply.

Answer 1

You need to do this migration in 4 steps:
And regarding your data transfer code - it is not recommended to do this because, firstly, it is extremely slow, and secondly, migration depends on the class. Imagine, after a while you modify the class (for example, rename the query_date attribute) and your migration will stop working. it relies on the fact that the code of the Page class has not changed, but it has.
In order for migrations not to depend on class code and not fall off over time, you need to write them in SQL.

execute('update pages set new_query_date = STR_TO_DATE(query_date)')