How to set MySQL to use GMT in Windows and Linux

Question

I'm just trying to get MySQL to store time in GMT...

I've read the documentation here: http://dev.mysql.com/doc/refman/5.1/en/time-zone-support.html

It says to set: default-time-zone='timezone' in an option file.

However, I've googled for several different terms and cannot find possible values "timezone" is supposed to be. I also don't know where in my.ini (and in Linux, my.cnf) to put this directive. Below [msyqld] ?

Answer 1

The MySQL Timezone table is not loaded by default, which might be why you're experiencing difficulties. You need to load the timezone table and then set your timezone using the instructions above.

Answer 2

Go to [mysqld] section of your my.ini file and under that section enter this line

default-time-zone = '+00:00'

'+00:00' indicates the offset from GMT which in your case will be 0. Please note the '+' sign in the string.

You don't need to install any timezone tables for your problem. After restarting the server, your server will operate in the UTC timezone and hence NOW() will give you the time in GMT.

By default, MySQL is set to your SYSTEM timezone ie. your server timezone is same as your system timezone. So you could also change your system time zone to solve your problem, though this is not recommendable.

Answer 3

Yes, that's a server option for mysqld, and so should be put in the [mysqld] section.

Also, regarding values, see The Definitive Guide to MySQL 5

Answer 4

Just to save a few clicks, the list of zone names is shown like this:

select name from mysql.time_zone_name;

Beware of setting a fixed-offset zone such as 'GMT' or '+00:00' since it will not alter to match local time / DST etc. If you want it to follow local time, set the zone to 'Europe/London' instead. If you don't want that I'd go for UTC over GMT anyway.

Answer 5

I use this for GMT+2: SET GLOBAL time_zone = '+02:00';

Answer 6

Screw this... DATETIME, although much easier on the eye when viewing tables, isn't worth this nightmare.

I'm just going to use TIMESTAMP.