Search code examples
mysqldatabasedatetimetimezoneinformation-schema

Mysql DB - what needs to be done in the database when changing server's timezone


I'm about to change my server timezone. We are saving times in local time and have decided to change all our time managing to UTC time. As part of this process, I am scanning my schema, for dateTime & timestamp type columns, in order to shift them from local time to UTC.

My question is - Is there some changes needs to be done in the other schemas of the DB like information_schema or mysql?


Solution

  • No, you don't need to change the built-in pseudo tables with one exception: the time zone tables. If they are not loaded you must load them. Try SET time_zone 'Asia/Kolkata'; or something similar. If it fails your time zone tables are not loaded. You can read about loading them.

    DO NOT TRY TO CHANGE other built-in tables. At best the changes will be rejected. At worst you'll trash your server.

    Was your server's host operating system time already running on UTC before this change? If so, you do not need to change values in your TIMESTAMP columns; those values are already stored internally in UTC and converted to the local time zone for you on the way in and out.