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
To see what value they are set to:
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.
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
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?