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