Will changing a MySQL timezone change values of DateTime fields in a database?

Question

I have a MySQL database that is set to a local time zone. Times are inserted as UTC time although the database uses a different time zone. I would like to change the time zone to UTC on the MySQL server. I have found documentation on how to do this, but am reluctant as I do not know if that would also change the values that are already stored in the Database.

My Question: Will changing the time zone of a MySQL server also change the values that are already stored?

Answer 1

In principle it shouldn't. There are various reasons why it shouldn't change, depending on the type of your values : mostly DATETIME and TIMESTAMP.

DATETIME values are never converted, so they are independent of the time zone.

TIMESTAMP values are converted (direct quote from the manual here --- I assume you have a fairly recent version of MySQL) "from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME.) By default, the current time zone for each connection is the server's time. The time zone can be set on a per-connection basis. As long as the time zone setting remains constant, you get back the same value you store. If you store a TIMESTAMP value, and then change the time zone and retrieve the value, the retrieved value is different from the value you stored. This occurs because the same time zone was not used for conversion in both directions." (from http://dev.mysql.com/doc/refman/5.5/en/datetime.html).

So in both cases the data actually stored on the server does not change (which is as it should be), but the values that your queries will show may be different before and after.

Answer 2

It depends on whether you are using TIMESTAMP or DATETIME columns to store your timestamps.

TIMESTAMP times are translated automatically from local time (precisely: from the connection's time zone setting) to UTC time when they are stored in tables to MySQL. They're translated from UTC to local time when they are retrieved.

But DATETIME timestamps are stored and retrieved exactly as your application presented them to MySQL. So, if you change your default timezone to UTC and then retrieve your TIMESTAMP values, you'll get them back in UTC. So they will look like they changed. But what has changed is the automatic translation.

Changing the MySQL default timezone does not alter any table contents.

You can experiment with this by issuing the following command when connecting to your MySQL.

 SET  time_zone = '+0:00';

That will change your connection's time zone setting without changing anything else.

Be careful when reconfiguring a production server that you know what you're doing: You say your "MySQL database ... is set to a local time zone." You need to investigate exactly how that is set up before you change anything. Because here are the settings that can affect things.

(1) the clock setting on the server machine running your MySQL server software.

(2) the timezone setting on that machine.

(3) the default (aka global) timezone setting in your running MySQL server.

In most modern servers the clock is set to the correct UTC time and the timezone setting is set to whatever local timezone your users expect. And, the default timezone setting for your MySQL server is set to that same local timezone. You need to verify that those things are correct.

If your server's time of day flips over automatically and correctly from standard to daylight savings time (yesterday morning in the USA) the first two are probably right.

If you issue this MySQL command: SELECT @@global.time_zone' and get back either your local time zone name orSYSTEMthe third is right. Also issueSELECT NOW()` to double check. If you get the right time of day your system is probably fine. Finally, issue these two commands:

 SET  time_zone = '+0:00';
 SELECT NOW(); 

If you get the presently correct UTC time from your system, then everything is a known and good state, and you're ready to make the timezone switch.

Make the switch by changing the system_time_zone variable in your MySQL configuration file and restarting your MySQL server. See here for directions:

http://dev.mysql.com/doc/refman/5.1/en/time-zone-support.html