Magento / Web Apps

Fix Magento 1.9.x timezone after migration

by ,

We have by far the largest RPM repository with dynamic stable NGINX modules and VMODs for Varnish 4.1 and 6.0 LTS. If you want to install NGINX, Varnish and lots of useful modules for them, this is your one stop repository to get all performance related software.
You have to maintain an active subscription in order to be able to use the repository!

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

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.