It seems that it does not matter what timezone is on the server as long as you have the time set right for the current timezone, know the timezone of the datetime columns that you store, and are aware of the issues with daylight savings time.
On the other hand if you have control of the timezones of the servers you work with then you can have everything set to UTC internally and never worry about timezones and DST.
Here are some notes I collected of how to work with timezones as a form of cheatsheet for myself and others which might influence what timezone the person will choose for his/her server and how he/she will store date and time.
Changing the timezone will not change the stored datetime or timestamp, but it will select a different datetime from timestamp columns
UTC does not use daylight savings time, GMT (the region) does, GMT (the timezone) does not (GMT is also confusing the definition of seconds which is why UTC was invented).
Warning! UTC has leap seconds, these look like '2012-06-30 23:59:60' and can be added randomly, with 6 months prior notice, due to the slowing of the earths rotation
Warning! different regional timezones might produce the same datetime value due to daylight savings time
The timestamp column only supports dates 1970-01-01 00:00:01 to 2038-01-19 03:14:07 UTC
When storing a date in a timestamp, MySQL will assume that the date is in the current session timezone and convert it to UTC for storage.
MySQL can store partial dates in datetime columns, these look like "2013-00-00 04:00:00"
MySQL stores "0000-00-00 00:00:00" if you set a datetime column as NULL, unless you specifically set the column to allow null when you create it.
no matter what timezone the current MySQL session is in:
SELECT CONVERT_TZ(`timestamp_field`, @@session.time_zone, '+00:00') AS `utc_datetime` FROM `table_name`
You can also set the sever or global or current session timezone to UTC and then select the timestamp like so:
SELECT `timestamp_field` FROM `table_name`
SELECT UTC_TIMESTAMP(); SELECT UTC_TIMESTAMP; SELECT CONVERT_TZ(NOW(), @@session.time_zone, '+00:00');
SELECT NOW(); SELECT CURRENT_TIMESTAMP; SELECT CURRENT_TIMESTAMP();
Returns "MSK" or "+04:00" for Moscow time for example, there is (or was) a MySQL bug where if set to a numerical offset it would not adjust the Daylight savings time
SELECT TIMEDIFF(NOW(), UTC_TIMESTAMP);
It will return 02:00:00 if your timezone is +2:00.
SELECT UNIX_TIMESTAMP(`timestamp`) FROM `table_name`
SELECT UNIX_TIMESTAMP(CONVERT_TZ(`utc_datetime`, '+00:00', @@session.time_zone)) FROM `table_name`
SELECT FROM_UNIXTIME(`unix_timestamp_int`) FROM `table_name`
SELECT CONVERT_TZ(FROM_UNIXTIME(`unix_timestamp_int`), @@session.time_zone, '+00:00') FROM `table_name`
Note: A timezone can be set in 2 formats:
- an offset from UTC: '+00:00', '+10:00' or '-6:00'
- as a named time zone: 'Europe/Helsinki', 'US/Eastern', or 'MET'
Named time zones can be used only if the time zone information tables in the mysql database have been created and populated.
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';
To set it use either one:
SET time_zone = 'Europe/Helsinki'; SET time_zone = "+00:00"; SET @@session.time_zone = "+00:00";
both "@@global.time_zone variable" and "@@session.time_zone variable" 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
Note: you can not do this as it will return NULL:
SELECT CONVERT_TZ(`timestamp_field`, TIMEDIFF(NOW(), UTC_TIMESTAMP), '+00:00') AS `utc_datetime` FROM `table_name`
Note: According to the docs, the value you set for time_zone does not change, if you set it as "+01:00" for example, then the time_zone will set as an offset from UTC, which does not follow DST, so I assume that it will stay the same all year round.
You can read more about working with DST here