Changing the field date type from 'datetime' to 'unix timestamp' without losing data 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:


Update the field type in my feature: MYFEATURE.features.field.inc // changed to timestamp [EASY]
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.


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' =&gt; 'int', //or timestamp
    'not null' =&gt; TRUE,
    'unsigned' =&gt; TRUE,
    'default' =&gt; 0,
    'description' =&gt; '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)
      -&gt;fields(array(
          'DATE2' =&gt; strtotime('DATE'),
         )
         )
       -&gt;execute();
      db_drop_field($table, 'DATE');
}
}
The strtotime() php function will be helpful for converting date to unix timestamp