change the VARCHAR field type to DATE

Two years ago, the database was created, now I want to put it in order a little.

It was incorrectly chosen (due to inexperience) to store the date in a regular varchar, the record goes there as a UNIXTIME number.

I want to change the type of the VARCHAR field to DATE.

Is it possible to do this quickly through the mysql query itself in phpMyAdmin? Or do I have to write a function, select each field, convert unix to YYYY-MM-DD, and write it back? And after that, change it type?

The number of rows is about 3000.

Author: Anton Shchyrov, 2018-02-25
Source

1 answers

The fastest way is

  1. Creating a new column of the TIMESTAMP/DATETIME type

  2. Executing the request

    UPDATE mytable SET new_col = FROM_UNIXTIME(old_col);
  3. Deleting the old column
  4. Rename the new column to the old name
 4
Author: Anton Shchyrov, 2018-02-25 10:18:30