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)
Best Answer
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
@@global.time_zone variable
To see what value they are set to:
To set a value for it use either one:
(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) andCEST
is a clock time that corresponds to a specific offset.@@session.time_zone variable
To set it use either one:
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
It will return 02:00:00 if your timezone is +2:00.
To get the current UNIX timestamp:
To get the timestamp column as a UNIX timestamp
To get a UTC datetime column as a UNIX timestamp
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?