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? 🙂