0

I'm working on a Rails app and would like to change the datatype for an existing column. It's currently a DateTime type, and I want to change it to a Date type. I found a way to do this here, but in this case, the person was not worried about preexisting data.

Right now, I plan to generate a migration...

rails g migration change_my_column_in_my_talbe

...and make the following change:

class ChangeMyColumnInMyTable < ActiveRecord::Migration
  def change
    change_column :my_table, :my_column, :date
  end
end

My question is: will the existing data be converted to a Date type, or will I need to create a rake task to convert the values for all of my existing DateTime values?

I found a similar question in which the conversion was from Boolean to String, and it seemed like the change would be automatic for existing data. I just want to be sure before I jump into making this change.

I'm using Rails version 4.2.0 and MySQL version 5.6.27 Homebrew. Any advice on this issue would be greatly appreciated!

Community
  • 1
  • 1
tinezekis
  • 93
  • 2
  • 9

2 Answers2

2

change_column is going to be translated into an ALTER TABLE ... ALTER COLUMN SQL statement, at which point how the casting is handled is up to your database, so the full answer depends on which database you're using. However, it's a pretty safe bet that your database can convert between datetime and date without trouble.

That said, there's no better way to know than to test it!

Robert Nubel
  • 7,104
  • 1
  • 18
  • 30
1

Using the change_column method, data conversion will be handled by the specific database adapter you are using. For example, with mysql adapter, change_column will call ALTER TABLE tbl_name MODIFY COLUMN col_name DATE. The data conversion from DATETIME to DATE will truncate the times.

Futhermore, MYSQL DATETIME to DATE performs rounding, conversion to a DATE value takes fractional seconds into account and rounds the time, '1999-12-31 23:59:59.499' becomes '1999-12-31', whereas '1999-12-31 23:59:59.500' becomes '2000-01-01'. Either way, the change method IS NOT reversible for the change_column method.

http://guides.rubyonrails.org/active_record_migrations.html#using-the-change-method

If you plan on reversing this migration, use the reversible method instead.

SacWebDeveloper
  • 2,563
  • 2
  • 18
  • 15
  • I am using `mysql`; I should have included that in my original question. Thank you for this explanation and for providing the link! – tinezekis Mar 03 '16 at 17:25