How Data Type Changes Affect Existing Data in Rails

datatypesrailstype conversion

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!

Best Answer

I was able to set up a way to test this, and the existing data did convert from DateTime to Date types! As explained here, when doing this with MySQL as the adapter, using change_table triggers the following SQL call:

ALTER TABLE table_name MODIFY COLUMN column_name DATE

Two helpful warnings (courtesy of @SacWebDeveloper):

This is an irreversible change, so if anyone else is trying this and may need to reverse, be sure to use the reversible command or write out the up and down changes.
When converting from DateTime to Date, be aware that rounding uses fractions of a second. Example: '1999-12-31 23:59:59.499' => '1999-12-31', but '1999-12-31 23:59:59.500' => '2000-01-01'.

Related Solutions

Mysql – How to compare (in MySQL) a DATETIME value to a TIMESTAMP value

There is a manual page on Time and Date functions.

The best way to compare date and time and get minutes back is probably to use one of the following:

SELECT TIME_TO_SEC(TIMEDIFF(timea,timeb)+0)/60
SELECT TIMESTAMPDIFF(MINUTE,timeb,timea)

Note that while TIMEDIFF works with times, TIMESTAMPDIFF requires a date component - and the values are switched around for TIMESTAMPDIFF.

You can return hours with either of these:

SELECT TIME_TO_SEC(TIMEDIFF(timea,timeb)+0)/60/60
SELECT TIMESTAMPDIFF(HOUR,timeb,timea)

Note that when using DATEDIFF, the value returned is a difference between the date components of the given time - thus, a value of 23:59 on one day compared to a value of 0:01 on the next (a 2 minute difference) results in a date difference of 1 (a full day).

To get a difference between two times in 24-hour periods (length of a day) first calculate the difference in minutes then divide by the proper values to find the number of 24-hour periods. Try this:

SELECT TRUNCATE(TIMESTAMPDIFF(MINUTE,timeb,timea)/60.0/24.0,0)

This truncates the decimal portion, returning only the number of complete 24-hour periods in the specified timespan.

Sql-server – Change column data type across database

No, there's no magic or hand-waving here. It'd be great if synonyms, say, applied to types, but that is not the case. If you want to make these columns first-class citizens, you'll need to change the table. You can automate this to some degree, though I won't post code to help with this unless you specify what you mean exactly by "manually" and why you think this will be a significant burden you want to avoid.

To automate this, you could say:

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += CHAR(13) + CHAR(10) + N'ALTER TABLE '
  + QUOTENAME(OBJECT_SCHEMA_NAME([object_id]))
  + '.' + QUOTENAME(OBJECT_NAME([object_id])) 
  + ' ALTER COLUMN ' + QUOTENAME(name) + ' NVARCHAR(MAX)'
  + CASE is_nullable WHEN 0 THEN ' NOT NULL;' ELSE ';' END
FROM sys.columns
WHERE system_type_id = 99;

PRINT @sql;

-- EXEC sp_executesql @sql;

That said, you probably don't want to use these columns with distinct / group by anyway.

Best Answer

Related Solutions

Mysql – How to compare (in MySQL) a DATETIME value to a TIMESTAMP value

Sql-server – Change column data type across database

Related Question