Changing the field date type from 'datetime' to 'unix timestamp' without losing data

Question

I am investigating how could I convert the date format in the 4 database tables: field_data_field_article_publication_date from the actual datetime format: 2013-09-10 12:43:03 to its unix timestamp equivalent: 1378816983 with a minimal negative impact on data integrity of the actual articles on a production environment.

UPDATE

---

My guess scenario would be:

1. Update the field type in my feature: MYFEATURE.features.field.inc // changed to timestamp [EASY]

2. Alter the table field structure from 'datetime' to 'timestamp' in a hook_schema_alter [EASY]. This should be performed using db_change_field().

RISKY AS I CAN LOSE DATA WHEN MOVING CONTENT. I MIGHT TRY TO DUPLICATE COLUMN DATE (INTO DATE2) FIRST.

1. I NEED HELP HERE: Convert all existing dates for articles found in 'field_data_field_article_publication_date' table to the new format unix stamp. [NOT SURE HOW]

Any further help?

I am interested in minimizing querying time by using MYSQL: UNIX_TIMESTAMP(date) somehow.

My approach on No.3

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

}

That would be a start in MYMODULE.install:

function MYMODULE_schema_alter() {
//...loop over 4 tables
  $schema['TABLENAME1']['fields']['DATE2'] = array(
    'type' => 'int', //or timestamp
    'not null' => TRUE,
    'unsigned' => TRUE,
    'default' => 0,
    'description' => 'Field added by my_module',
  );
//…
}

/**
 * Content types using date fields should be using timestamp instead datetime
 */
function MYMODULE_update_7192() {
//define their names or get them from schema
$tables = array('TABLENAME1', 'TABLENAME2', 'TABLENAME3', 'TABLENAME4');


foreach ($tables as $table) {
       //create a new column date2 of type: int (or datetime?)
       $schema = drupal_get_schema($table);
       db_add_field($table, 'DATE2', $schema['fields']['DATE2']);
       db_update($table)
      ->fields(array(
          'DATE2' => strtotime('DATE'),
         )
         )
       ->execute();
      db_drop_field($table, 'DATE');
}
}

Answer 1

The strtotime() php function will be helpful for converting date to unix timestamp