Set MySQL database timezone to GMT

Question

I need to change the time zone for one database, is this possible?

I know that we can change the time zone in our WHM (we use a dedicated server from hostgator), however a large amount of outdated software running on the server has at its disposal many +6 hours (for example, the server time zone CST, we need time in GMT, so previous developers changed the date / time manually in the code - bad!).

Now I am working on new software and would like to have it in GMT, I know I can use date_default_timezone_set ('GMT'), however this will not solve MySQL insertion, where the datetime column is set to CURRENT_TIMESTAMP, since it will be insert @ timestamp CST .

Answer 1

No, it is not possible to change the time zone for a single database in a MySQL instance.

You can get time_zone server and client time_zone :

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

You can also change the client’s time zone or time zone for the entire instance of MySQL.

But be extremely aware of the implication that is relevant to existing client connections, and how the DATETIME and TIMESTAMP values ​​already stored in the instance will be interpreted.

In order for the time_zone server to be installed when starting the MySQL instance, modify the /etc/my.cnf file (or wherever your mysql initialization parameters are read) in the [mysqld] section:

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

- or -

add the parameter --default_time_zone='+00:00' to mysqld_safe

NOTE. Changing the time zone setting on the MySQL server does NOT change the values ​​stored in existing DATETIME or TIMESTAMP columns, BUT, since it effectively changes the context in which these stored values ​​are interpreted, it will look as if all ARE values ​​are shifted. (In cases where 08:00 was considered the value of 8AM CST, with the change of server_zone from CST to GMT, the same '08: 00 'will now be considered 8AM GMT, which will be 2AM CST.

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

Each client session can change the time zone setting for its own session:

 SET time_zone='-06:00'; 

---

But none of this "solves" the problem of changing the time zone, it simply moves the problem of conversion.

Nothing is initially "bad" when processing calls to the time zone using the application level; sometimes what is the best place to handle. This must be done correctly and consistently.

(What is strange about the setting you are describing is that the application stores DATETIME values ​​as if the MySQL time_zone server is set to GMT, but something else is set for the MySQL time_zone server.)

Answer 2

If you cannot change the current time zone, you can change the result.

Date 2015-01-05 12:06:16

 Select date + Interval 2 Hour 'date' From ExampleTable 

Date 2015-01-05 14:06:16

Answer 3

You can change the default value instead of entering current_timestamp so that it adds current_timestamp, added to the time offset of your time zone. I just did it when I couldn’t find a solution, I had to come up with my own.