Correcting incorrect timestamp after server migration

An issue we (admittedly infrequently) come across when moving Magento stores from one server to another, is when the previous server's time was incorrect - causing all historical order data to be incorrect on MySQL import.

More often than not, we see stores migrated from US West (-7:00) show orders that are in the future after migration; because of discrepancies on the MySQL server locale, the Magento locale and the Web server locale. There are two opportunities to fix this, either prior to taking the DB dump (or prior to import) - or once the DB has been imported.

After DB Import

This can be relatively easily remedied by performing a post-side migration correction on the order tables, specifically sales_flat_order and sales_flat_order_grid. We only change these two tables because they are the most sensitive to date/time changes.

UPDATE sales_flat_order SET created_at = ADDTIME(created_at , '-07:00:00'), updated_at = ADDTIME(updated_at , '-07:00:00');
UPDATE sales_flat_order_grid SET created_at = ADDTIME(created_at , '-07:00:00'), updated_at = ADDTIME(updated_at , '-07:00:00');

You can repeat this on as many tables as necessary (eg. Magento Enterprise's admin action log).

Prior to DB Dump/Import

Alternatively, other simpler solutions involve dumping the DB with --skip-tz-utc or setting the timezone before/after import - which will ensure all timestamp fields will have the correct information.