問題描述
在一臺(tái)服務(wù)器上，當(dāng)我運(yùn)行時(shí):
On one server, when I run:
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2009-05-30 16:54:29 |
+---------------------+
1 row in set (0.00 sec)
在另一臺(tái)服務(wù)器上:
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2009-05-30 20:01:43 |
+---------------------+
1 row in set (0.00 sec)
推薦答案
我認(rèn)為這可能有用:
default-time-zone='+00:00'
@@global.time_zone 變量
查看它們?cè)O(shè)置的值:
@@global.time_zone variable
To see what value they are set to:
SELECT @@global.time_zone;
要為其設(shè)置一個(gè)值，請(qǐng)使用其中之一:
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';
(使用諸如歐洲/赫爾辛基"之類的命名時(shí)區(qū)意味著您必須正確填充時(shí)區(qū)表.)
(Using named timezones like 'Europe/Helsinki' means that you have to have a timezone table properly populated.)
請(qǐng)記住，+02:00 是一個(gè)偏移量.Europe/Berlin 是一個(gè)時(shí)區(qū)(有兩個(gè)偏移量)，CEST 是對(duì)應(yīng)于特定偏移量的時(shí)鐘時(shí)間.
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.
SELECT @@session.time_zone;
要設(shè)置它，請(qǐng)使用其中之一:
To set it use either one:
SET time_zone = 'Europe/Helsinki';
SET time_zone = "+00:00";
SET @@session.time_zone = "+00:00";
兩者都可能返回 SYSTEM，這意味著它們使用 my.cnf 中設(shè)置的時(shí)區(qū).
Both might return SYSTEM which means that they use the timezone set in my.cnf.
要使時(shí)區(qū)名稱起作用，您必須設(shè)置需要填充的時(shí)區(qū)信息表:http://dev.mysql.com/doc/refman/5.1/en/time-zone-support.html.我還在這個(gè)答案中提到了如何填充這些表格.
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.
SELECT TIMEDIFF(NOW(), UTC_TIMESTAMP);
如果您的時(shí)區(qū)為 +2:00，它將返回 02:00:00.
It will return 02:00:00 if your timezone is +2:00.
SELECT UNIX_TIMESTAMP();
SELECT UNIX_TIMESTAMP(NOW());
獲取時(shí)間戳列作為 UNIX 時(shí)間戳
SELECT UNIX_TIMESTAMP(`timestamp`) FROM `table_name`
獲取 UTC 日期時(shí)間列作為 UNIX 時(shí)間戳
SELECT UNIX_TIMESTAMP(CONVERT_TZ(`utc_datetime`, '+00:00', @@session.time_zone)) FROM `table_name`
注意:更改時(shí)區(qū)不會(huì)更改存儲(chǔ)的日期時(shí)間或時(shí)間戳，但它會(huì)為現(xiàn)有時(shí)間戳列顯示不同的日期時(shí)間，因?yàn)樗鼈冊(cè)趦?nèi)部存儲(chǔ)為 UTC 時(shí)間戳并在當(dāng)前 MySQL 中外部顯示時(shí)區(qū).
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.
我在這里做了一個(gè)備忘單:MySQL應(yīng)該有它的時(shí)區(qū)設(shè)置為 UTC?
I made a cheatsheet here: Should MySQL have its timezone set to UTC?
這篇關(guān)于如何設(shè)置 MySQL 的時(shí)區(qū)?的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！