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' => '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');
}
}