Rails migration change time to datetime preserving data-CodePudding

I need to change my column 'start_time' from time to datetime (Am I correct in that datetime and timestamp are the same thing? NOTE: I'm on postgres)

class ChangeStartTimeToBeDatetimeInAssignments < ActiveRecord::Migration[5.0]
  def up
    change_column :assignments, :start_time, :datetime
  end
  def down
    change_column :assignments, :start_time, :time
  end
end

The data in the column is important so it can't be deleted...

Note: here is a snippet of the json output displaying only the "start_time" portion (if it matters)

"start_time":"2000-01-01T12:00:00"

This error occurs when I migrate:

PG::DatatypeMismatch: ERROR:  column "start_time" cannot be cast automatically to type timestamp without time zone
HINT:  You might need to specify "USING start_time::timestamp without time zone".

Running this migration...

class ChangeStartTimeToBeDatetimeInAssignments < ActiveRecord::Migration[5.0]
  def up
    change_column :assignments, :start_time, :datetime, 'USING start_time::timestamp without time zone'
  end
  def down
    change_column :assignments, :start_time, :time
  end
end

Gives this error...

== 20210916230930 ChangeStartTimeToBeDatetimeInAssignments: migrating =========
-- change_column(:assignments, :start_time, :datetime, "USING start_time::timestamp without time zone")
rake aborted!
StandardError: An error has occurred, this and all later migrations canceled:

no implicit conversion of Symbol into Integer

What am I doing wrong?

How do I migrate "time" to "datetime"?

How do I modify my migration and add "USING start_time::timestamp without time zone" as suggested?

(Am I correct in that datetime and timestamp are the same thing?)

Thank you for your time.

CodePudding user response：

For the sake of readability just make everything step by step. This also will help with defining a proper down method to make sure rollbacks won't error out.

class ChangeStartTimeToBeDatetimeInAssignments < ActiveRecord::Migration[5.0]
  def up
    # add a temporary column
    add_column :assignments, :start_time_datetime, :datetime
    
    # add the the current start_time as datetime to the temporary column for each entry
    Assignment.all.each do |assignment|
      assignment.update(start_time_datetime: assignment.start_time.to_datetime)
    end

    # drop the old time column
    remove_column :assignments, :start_time
    
    # rename the temporary column to start_time   
    rename_column :assignments, :start_time_datetime, :start_time
  end

  def down
    add_column :assignments, :start_time_time, :time
    
    Assignment.all.each do |assignment|
      assignment.update(start_time_time: assignment.start_time.to_time
    end

    remove_column :assignments, :start_time
    rename_column :assignments, :start_time_time, :start_time
  end
end

To ensure that the update loop does not error out because of data inconsistency and leaving you with half messed up data you can wrap the update part into a https://api.rubyonrails.org/v6.1.4/classes/ActiveRecord/Transactions/ClassMethods.html