I recently migrated from Drupal to Wordpress on this site, and needed to migrate all of my old content to the new database. However, One of the problems with migrating the data from one content management system to another is dealing with the differences in database schema.
Drupal stores content timestamps as an int(11) field, which represents the number of seconds since epoch (midnight, January 1st 1970). Wordpress uses a datetime field. As the two formats are completely different, a conversion must take place. Drupal stores content in the node table, and the field that stores the content creation date is called created. We’ll need to select that field, and use the MySQL function FROM_UNIXTIME() to convert the date. Here’s how you do it:
mysql> SELECT title,type,FROM_UNIXTIME(created) as created,body FROM > node ORDER BY created; +-----------------------------------+--------+---------------------+ | title | type | created | +-----------------------------------+--------+---------------------+ | Story1 | story | 2005-03-10 11:28:22 | | Story2 | story | 2005-03-11 16:25:14 | | A New Story | story | 2005-03-11 16:31:18 | +-----------------------------------+--------+---------------------+
The FROM_UNIXTIME() function also supports formatting of the result:
mysql> SELECT FROM_UNIXTIME(created, '%Y %D %M %h:%i:%s %x');
+--------------------------------------------------------------+ | FROM_UNIXTIME(created, '%Y %D %M %h:%i:%s %x') | +--------------------------------------------------------------+ | 2005 10th March 11:05:32 2005 |
+--------------------------------------------------------------+
To reverse the conversion, you would use the UNIX_TIMESTAMP() function to convert your datetime field to a Drupal-style int(11) timestamp.
tags: data+conversion, database, date+functions,from_unixtime(),content+management+system, drupal, migration, mysql, wordpress
