How do I set the time zone of MySQL?

Question

On one server, when I run:

mysql> select now(); +---------------------+ | now()               | +---------------------+ | 2009-05-30 16:54:29 | +---------------------+ 1 row in set (0.00 sec) 

On another server:

mysql> select now(); +---------------------+ | now()               | +---------------------+ | 2009-05-30 20:01:43 | +---------------------+ 1 row in set (0.00 sec) 

Answer 1

I thought this might be useful:

There are three places where the timezone might be set in MySQL:

In the file "my.cnf" in the [mysqld] section

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

@@global.time_zone variable

To see what value they are set to:

SELECT @@global.time_zone; 

To set a value for it use either one:

SET GLOBAL time_zone = '+8:00'; SET GLOBAL time_zone = 'Europe/Helsinki'; SET @@global.time_zone = '+00:00'; 

(Using named timezones like 'Europe/Helsinki' means that you have to have a timezone table properly populated.)

Keep in mind that +02:00 is an offset. Europe/Berlin is a timezone (that has two offsets) and CEST is a clock time that corresponds to a specific offset.

@@session.time_zone variable

SELECT @@session.time_zone; 

To set it use either one:

SET time_zone = 'Europe/Helsinki'; SET time_zone = "+00:00"; SET @@session.time_zone = "+00:00"; 

Both might return SYSTEM which means that they use the timezone set in my.cnf.

For timezone names to work, you must setup your timezone information tables need to be populated: http://dev.mysql.com/doc/refman/5.1/en/time-zone-support.html. I also mention how to populate those tables in this answer.

To get the current timezone offset as TIME

SELECT TIMEDIFF(NOW(), UTC_TIMESTAMP); 

It will return 02:00:00 if your timezone is +2:00.

To get the current UNIX timestamp:

SELECT UNIX_TIMESTAMP(); SELECT UNIX_TIMESTAMP(NOW()); 

To get the timestamp column as a UNIX timestamp

SELECT UNIX_TIMESTAMP(`timestamp`) FROM `table_name` 

To get a UTC datetime column as a UNIX timestamp

SELECT UNIX_TIMESTAMP(CONVERT_TZ(`utc_datetime`, '+00:00', @@session.time_zone)) FROM `table_name` 

Note: Changing the timezone will not change the stored datetime or timestamp, but it will show a different datetime for existing timestamp columns as they are internally stored as UTC timestamps and externally displayed in the current MySQL timezone.

I made a cheatsheet here: Should MySQL have its timezone set to UTC?

Answer 2

For anyone still having this issue:

value="jdbc:mysql://localhost:3306/dbname?serverTimezone=UTC" 

Worked for me. Just append ?serverTimezone=UTC at the end.