From: 011netservice@gmail.com
Date: 2022-04-24
Subject: Time_Zone.txt, 歡迎來信交流.

----------
20200611
time_zone 時區

查詢( global 或 session) level 時區: 
mysql> SELECT @@global.time_zone, @@session.time_zone;
# @@global.time_zone, @@session.time_zone
'SYSTEM', 'SYSTEM'

SELECT @@global.time_zone, TIMEDIFF(NOW(), UTC_TIMESTAMP), CURRENT_TIMESTAMP, utc_timestamp()
# @@global.time_zone, TIMEDIFF(NOW(), UTC_TIMESTAMP), CURRENT_TIMESTAMP,     utc_timestamp()
'SYSTEM',             '08:00:00',                     '2020-06-11 14:21:16', '2020-06-11 06:21:16'


設定( global 或 session) level 時區:
mysql> SET @@session.time_zone='+00:00';

mysql> SELECT @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| SYSTEM             | +00:00              |
+--------------------+---------------------+

修改 (my.cnf) 設定檔後, 重新啟動 MySQL, 可永久變更 MySQL 時區:
Or permanently in your my.cnf:
[mysqld]
**other variables**
default_time_zone='+00:00'


ref:
https://stackoverflow.com/questions/930900/how-do-i-set-the-time-zone-of-mysql

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
取得目前時區跟 UTC 時區的差距
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.

ref:
https://stackoverflow.com/questions/19023978/should-mysql-have-its-timezone-set-to-utc/19075291#19075291

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.

MySQL Timezone Cheatsheet
Notes:

Changing the timezone will not change the stored datetime or timestamp, but it will select a different datetime from timestamp columns
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
GMT confuses seconds, which is why UTC was invented.

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, due to a limitation.
Internally a MySQL timestamp column is stored as UTC but when selecting a date MySQL will automatically convert it to the current session timezone.

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.
Read this
To select a timestamp column in UTC format
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`
To select the current datetime in UTC:
SELECT UTC_TIMESTAMP();
SELECT UTC_TIMESTAMP;
SELECT CONVERT_TZ(NOW(), @@session.time_zone, '+00:00');
Example result: 2015-03-24 17:02:41

To select the current datetime in the session timezone
SELECT NOW();
SELECT CURRENT_TIMESTAMP;
SELECT CURRENT_TIMESTAMP();
To select the timezone that was set when the server launched
SELECT @@system_time_zone;
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

To get the current timezone
SELECT TIMEDIFF(NOW(), UTC_TIMESTAMP);
It will return 02:00:00 if your timezone is +2:00.

To get the current UNIX timestamp (in seconds):
SELECT UNIX_TIMESTAMP(NOW());
SELECT UNIX_TIMESTAMP();
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`
Get a current timezone datetime from a positive UNIX timestamp integer
SELECT FROM_UNIXTIME(`unix_timestamp_int`) FROM `table_name`
Get a UTC datetime from a UNIX timestamp
SELECT CONVERT_TZ(FROM_UNIXTIME(`unix_timestamp_int`), @@session.time_zone, '+00:00') 
FROM `table_name`
Get a current timezone datetime from a negative UNIX timestamp integer
SELECT DATE_ADD('1970-01-01 00:00:00',INTERVAL -957632400 SECOND) 
There are 3 places where the timezone might be set in MySQL:
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.

in the file "my.cnf"
default_time_zone='+00:00'
or

timezone='UTC'
@@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';
@@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 "@@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 (even for default-time-zone) 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`
Setup mysql timezone tables
For CONVERT_TZ to work, you need the timezone tables to be populated

SELECT * FROM mysql.`time_zone` ;
SELECT * FROM mysql.`time_zone_leap_second` ;
SELECT * FROM mysql.`time_zone_name` ;
SELECT * FROM mysql.`time_zone_transition` ;
SELECT * FROM mysql.`time_zone_transition_type` ;
If they are empty, then fill them up by running this command

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql
if this command gives you the error "data too long for column 'abbreviation' at row 1", then it might be caused by a NULL character being appended at the end of the timezone abbreviation

the fix being to run this

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql
(if the above gives error "data too long for column 'abbreviation' at row 1")
mysql_tzinfo_to_sql /usr/share/zoneinfo > /tmp/zut.sql

echo "SET SESSION SQL_MODE = '';" > /tmp/mysql_tzinfo_to.sql
cat /tmp/zut.sql >> /tmp/mysql_tzinfo_to.sql

mysql --defaults-file=/etc/mysql/my.cnf --user=verifiedscratch -p mysql < /tmp/mysql_tzinfo_to.sql
(make sure your servers dst rules are up to date zdump -v Europe/Moscow | grep 2011 https://chrisjean.com/updating-daylight-saving-time-on-linux/)

See the full DST (Daylight Saving Time) transition history for every timezone
SELECT 
tzn.Name AS tz_name,
tztt.Abbreviation AS tz_abbr,
tztt.Is_DST AS is_dst,
tztt.`Offset` AS `offset`,
DATE_ADD('1970-01-01 00:00:00',INTERVAL tzt.Transition_time SECOND)  AS transition_date
FROM mysql.`time_zone_transition` tzt
INNER JOIN mysql.`time_zone_transition_type` tztt USING(Time_zone_id, Transition_type_id)
INNER JOIN mysql.`time_zone_name` tzn USING(Time_zone_id)
-- WHERE tzn.Name LIKE 'Europe/Moscow' -- Moscow has weird DST changes
ORDER BY tzt.Transition_time ASC
CONVERT_TZ also applies any necessary DST changes based on the rules in the above tables and the date that you use.

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 be set as an offset from UTC, which does not follow DST, so it will stay the same all year round.

Only the named timezones will change time during daylight savings time.

Abbreviations like CET will always be a winter time and CEST will be summer time while +01:00 will always be UTC time + 1 hour and both won't change with DST.

The system timezone will be the timezone of the host machine where mysql is installed (unless mysql fails to determine it)