2

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');
}
}
  • You mean to update without changing the datatype? – Daniel Vérité Sep 10 '13 at 17:22
  • @DanielVérité No, I assume I need to perform a hook_schema_alter before, not sure about the priority as I don't want to loose data. – drupal_stuff_alter Sep 11 '13 at 7:14
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.