How to change field type from integer to datetime with automatic value conversion?

Question

MySQL

Tell me, there is a database, the date_of_birth field type is specified as integer and all dates in it as a number unixtimestamp
I need to change the field type to datetime
If you do
ALTER TABLE tablename MODIFY date_of_birth DATETIME;
gives an error invalid value
Is there any solution to automatically convert the date from integer to datetime in the table?

Answer 1

mysql does not know how to convert values ​​when changing the type to an incompatible one.
Therefore, you need to add a new field, update the entire table, delete the old field, and rename the new one to the old one. There is a suitable FROM_UNIXTIME
function for converting from numeric unixtime to datetime , so updating the table would be:

update tablename set new_datefield = FROM_UNIXTIME(old_int_unixtime);

Answer 2

As an option:
1. Add a new field next
to it 2. UPDATE to transfer the value in the required format
3. Demolish the old field
4. Rename the new field to the old one