The proper server timezone for Magento server (or any out there, really) is UTC.
But often times, I see that server admins neglect that rule. They set server timezone to match with Magento, or worse, their own location. By doing so, they introduce nothing but trouble when the time comes to change server.
How to fix Magento timezone after migration?
Let’s take an original server with server timezone set to Singapore. As a result, all the TIMESTAMP columns in Magento will be offset from UTC by +6 hours.
We need the proper TIMESTAMP columns with UTC timezone values on the new server, post data migration.
The fix would be to simply update all the TIMESTAMP columns by removing the bad offset:
UPDATE sales_flat_order SET created_at = ADDTIME(created_at , '-06:00:00'), updated_at = ADDTIME(updated_at , '-06:00:00');
UPDATE sales_flat_order_grid SET created_at = ADDTIME(created_at , '-06:00:00'), updated_at = ADDTIME(updated_at , '-06:00:00');
We have updated the most important columns. Your mileage might vary depending on a number of plugins you have.
To get all the TIMESTAMP in a database, use the following query:
select * from INFORMATION_SCHEMA.TABLES T where EXISTS
(
select 1
from INFORMATION_SCHEMA.COLUMNS
where TABLE_CATALOG = T.TABLE_CATALOG
and TABLE_SCHEMA = T.TABLE_SCHEMA
and TABLE_NAME = T.TABLE_NAME
and DATA_TYPE = 'timestamp' -- or the literal representing timestamp data type
and TABLE_SCHEMA = 'dbname'
)
TODO: an n98-magerun plugin? 🙂