PHP: Safely converting the field date in mySQL from 'datetime' to 'unix timestamp' without losing data

Question

PROBLEM

I need I safely convert the date format in the mySQL database table MYTABLE from the actual datetime format field date: e.g. 2013-09-10 12:43:03, to its unix timestamp equivalent: 1378816983 without compromising the existing thousands of articles already in the database.

I am using PHP, mySql, Drupal.

MY APPROACH

My steps

1. Alter the table field type from datetime to timestamp [DONE]

2. Converting the actual content type field: timestamp [DONE]

3. Converting existing article data field in 4 tables using PHP strtotime() or MYSQL: UNIX_TIMESTAMP(date)

I NEED HELP ON PT.3

A. a little help on the algorithm at the pt. 3:

e.g.

  loop over all 4 tables {

       - create a new column date2 of type: int (or datetime?) 
       - copy all dates from date to date2 passing them through strtotime()
       - delete column date
       - rename column date2 to column date

}

Answer 1

First off, the safest way to convert data like this is to not do it on the fly.

The first thing that I would do would be to add another column to your table. Then given that we aren't modifying existing data, I would look at using the Unix_timestamp mysql function. If you are storing the data in a date datatype already, it seems that it is nice and safe for mysql to do it.

Given that, it seems to be a simple matter of running something like:

update MyTable set newtimeStampColumn=UNIX_TIMESTAMP(oldDateColumn);

and see how it works. You can then run some comparison queries to ensure that all your data has converted corectly.

Answer 2

1. backup the table
2. check if backup is all right and safe
3. Alter the table field type from datetime to varchar
4. update table set field=unix_timestamp(field)
5. Alter the table field type from varchar to int

However I wouldn't do such a conversion at all