timezone - How do I set the time zone of MySQL? -

on 1 server, when run:

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

on server:

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

i thought might useful:

there 3 places timezone might set in mysql:

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

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

@@global.time_zone variable

to see value set to:

select @@global.time_zone; 

to set value use either one:

set global time_zone = '+8:00'; set global time_zone = 'europe/helsinki'; set @@global.time_zone = '+00:00'; 

(using named timezones 'europe/helsinki' means have have timezone table populated.)

keep in mind +02:00 offset. europe/berlin timezone (that has 2 offsets) , cest clock time corresponds specific offset.

@@session.time_zone variable

select @@session.time_zone; 

to set use either one:

set time_zone = 'europe/helsinki'; set time_zone = "+00:00"; set @@session.time_zone = "+00:00"; 

both might return system means use timezone set in my.cnf.

for timezone names work, must setup timezone information tables need populated: http://dev.mysql.com/doc/refman/5.1/en/time-zone-support.html. mention how populate tables in this answer.

to current timezone offset time

select timediff(now(), utc_timestamp); 

it return 02:00:00 if timezone +2:00.

to current unix timestamp:

select unix_timestamp(); select unix_timestamp(now()); 

to timestamp column unix timestamp

select unix_timestamp(`timestamp`) `table_name` 

to utc datetime column unix timestamp

select unix_timestamp(convert_tz(`utc_datetime`, '+00:00', @@session.time_zone)) `table_name` 

note: changing timezone will not change stored datetime or timestamp, show different datetime existing timestamp columns internally stored utc timestamps , externally displayed in current mysql timezone.

i made cheatsheet here: should mysql have timezone set utc?