Mysql – Set MySQL database timezone to GMT

databaseMySQLtimezone

I need to change the timezone of a single database is this possible?

I know we can change the timezone within our WHM (we are using a dedicated server from hostgator), however a large number of legacy software running on the server has a lot of +6 hours coding in it (i.e. server timezone is CST, we wanted the time in GMT so previous developers altered date/times manually within the code – bad!).

I am now working on a new software and would like to have it all in GMT, I know I can use date_default_timezone_set('GMT') however that will not solve MySQL inserts where the datetime column is set to CURRENT_TIMESTAMP as it will insert @ CST timezone.

Best Answer

No, it's not possible to change the timezone for a single database within a MySQL instance.

We can retrieve the server and client time_zone settings with a query, like this:

SELECT @@global.time_zone, @@session.time_zone;

We can also change the client timezone for a session, or change the timezone for the entire MySQL instance.

But we need to be keenly aware of the implication that this change will have on existing client connections, and the how DATETIME and TIMESTAMP values already stored in the instance will be interpreted.

To have the server time_zone set at MySQL instance startup, we can modify the /etc/my.cnf file (or wherever the mysql instance initialization parameters are read from), under the [mysqld] section:

[mysqld]
default-time-zone='+00:00'

-- or --

It is also possible (less desirable) to add the --default_time_zone='+00:00' option to mysqld_safe

NOTE: Changing the timezone setting on the MySQL server does NOT change the values stored in existing DATETIME or TIMESTAMP columns, BUT since it does effectively change the context in which those stored values are interpreted, it will look like all of the values ARE shifted. (Where 08:00 was taken to mean 8AM CST, with the time_zone of the server changed from CST to GMT, that same '08:00' will now be taken to be 8AM GMT, which would effectively be 2AM CST.

Also keep in mind that TIMESTAMP columns are always stored in UTC, while DATETIME columns do not have a timezone. http://dev.mysql.com/doc/refman/5.5/en/datetime.html

Each client session can change the timezone setting for their own session:

SET time_zone='-06:00';

But none of this really "solves" the timezone conversion problem, it just moves the conversion problem around.

There's nothing inherently "bad" with the application layer handling timezone conversions; sometimes, that's the best place to handle. It just has to be done correctly and consistently.

(What's odd about the setup you describe is that the app is storing DATETIME values as if the MySQL server time_zone is set to GMT, but the MySQL server time_zone is set to something else.)

Related Topic