問題描述

有誰知道MySQL中是否有這樣的功能?

Anyone knows if there is such a function in MySQL?

更新

這不會輸出任何有效信息:

This doesn't output any valid info:

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

或者MySQL本身不能準確知道使用的time_zone，沒關系，我們可以在此處涉及PHP，只要我能得到有效信息而不是<代碼>系統...

Or maybe MySQL itself can't know exactly the time_zone used,that's fine, we can involve PHP here, as long as I can get valid info not like SYSTEM...

推薦答案

來自手冊 (第 9.6 節):

全局和客戶端特定時區的當前值可以這樣檢索:
<代碼>mysql>選擇@@global.time_zone,@@session.time_zone;

The current values of the global and client-specific time zones can be retrieved like this:
mysql> SELECT @@global.time_zone, @@session.time_zone;

Edit 如果 MySQL 設置為使用系統的時區，則上面返回 SYSTEM，這沒有幫助.由于您使用的是 PHP，如果 MySQL 的答案是 SYSTEM，那么您可以通過 詢問系統 它是 使用的時區.net/manual/en/function.date-default-timezone-get.php" rel="noreferrer">date_default_timezone_get.(當然，正如 VolkerK 指出的那樣，PHP 可能運行在不同的服務器上，但根據假設，假設 Web 服務器和它與之通信的數據庫服務器 設置為 [如果實際上不是 >in] 相同的時區并不是一個巨大的飛躍.)但要注意(與 MySQL 一樣)，您可以設置 PHP 使用的時區(date_default_timezone_set)，這意味著它可能會報告一個與操作系統使用的值不同.如果您可以控制 PHP 代碼，您應該知道自己是否在這樣做并且沒問題.

Edit The above returns SYSTEM if MySQL is set to use the system's timezone, which is less than helpful. Since you're using PHP, if the answer from MySQL is SYSTEM, you can then ask the system what timezone it's using via date_default_timezone_get. (Of course, as VolkerK pointed out, PHP may be running on a different server, but as assumptions go, assuming the web server and the DB server it's talking to are set to [if not actually in] the same timezone isn't a huge leap.) But beware that (as with MySQL), you can set the timezone that PHP uses (date_default_timezone_set), which means it may report a different value than the OS is using. If you're in control of the PHP code, you should know whether you're doing that and be okay.

但是關于 MySQL 服務器使用哪個時區的整個問題可能是一個切線，因為詢問服務器它所在的時區告訴您完全沒有關于數據庫中的數據.繼續閱讀以了解詳情:

But the whole question of what timezone the MySQL server is using may be a tangent, because asking the server what timezone it's in tells you absolutely nothing about the data in the database. Read on for details:

進一步討論:

如果您在控制服務器，當然可以確保時區是已知數量.如果您無法控制服務器，您可以像這樣設置連接使用的時區:

If you're in control of the server, of course you can ensure that the timezone is a known quantity. If you're not in control of the server, you can set the timezone used by your connection like this:

set time_zone = '+00:00';

將時區設置為 GMT，以便任何進一步的操作(如 now())都將使用 GMT.

That sets the timezone to GMT, so that any further operations (like now()) will use GMT.

但是請注意，時間和日期值不與 MySQL 中的時區信息一起存儲:

Note, though, that time and date values are not stored with timezone information in MySQL:

mysql> create table foo (tstamp datetime) Engine=MyISAM;
Query OK, 0 rows affected (0.06 sec)

mysql> insert into foo (tstamp) values (now());
Query OK, 1 row affected (0.00 sec)

mysql> set time_zone = '+01:00';
Query OK, 0 rows affected (0.00 sec)

mysql> select tstamp from foo;
+---------------------+
| tstamp              |
+---------------------+
| 2010-05-29 08:31:59 |
+---------------------+
1 row in set (0.00 sec)

mysql> set time_zone = '+02:00';
Query OK, 0 rows affected (0.00 sec)

mysql> select tstamp from foo;
+---------------------+
| tstamp              |
+---------------------+
| 2010-05-29 08:31:59 |      <== Note, no change!
+---------------------+
1 row in set (0.00 sec)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2010-05-29 10:32:32 |
+---------------------+
1 row in set (0.00 sec)

mysql> set time_zone = '+00:00';
Query OK, 0 rows affected (0.00 sec)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2010-05-29 08:32:38 |      <== Note, it changed!
+---------------------+
1 row in set (0.00 sec)

所以知道服務器的時區只對獲取當前時間的函數很重要，例如now()、unix_timestamp()等.;它不會告訴您有關數據庫數據中日期使用的時區的任何信息.您可以選擇假設它們是使用服務器的時區編寫的，但這種假設很可能是有缺陷的.要了解存儲在數據中的任何日期或時間的時區，您必須確保它們與時區信息一起存儲，或者(像我一樣)確保它們始終采用 GMT.

So knowing the timezone of the server is only important in terms of functions that get the time right now, such as now(), unix_timestamp(), etc.; it doesn't tell you anything about what timezone the dates in the database data are using. You might choose to assume they were written using the server's timezone, but that assumption may well be flawed. To know the timezone of any dates or times stored in the data, you have to ensure that they're stored with timezone information or (as I do) ensure they're always in GMT.

為什么假設數據是使用服務器的時區寫入的?嗯，一方面，數據可能是使用設置不同時區的連接寫入的.數據庫可能已從一臺服務器移動到另一臺服務器，其中服務器位于不同的時區(當我繼承從德克薩斯州移動到加利福尼亞州的數據庫時遇到了這種情況).但是即使數據寫在服務器上，用它的當前時區，它仍然是不明確的.去年，在美國，夏令時在 11 月 1 日凌晨 2:00 關閉.假設我的服務器位于加利福尼亞，使用太平洋時區，并且我在數據庫中有 2009-11-01 01:30:00 值.那是什么時候?那是太平洋標準時間 11 月 1 日凌晨 1 點 30 分，還是太平洋標準時間 11 月 1 日凌晨 1 點 30 分(一小時后)?你完全沒有辦法知道.道德:始終以格林威治標準時間(不執行夏令時)存儲日期/時間，并在必要時轉換為所需的時區.

Why is assuming the data was written using the server's timezone flawed? Well, for one thing, the data may have been written using a connection that set a different timezone. The database may have been moved from one server to another, where the servers were in different timezones (I ran into that when I inherited a database that had moved from Texas to California). But even if the data is written on the server, with its current time zone, it's still ambiguous. Last year, in the United States, Daylight Savings Time was turned off at 2:00 a.m. on November 1st. Suppose my server is in California using the Pacific timezone and I have the value 2009-11-01 01:30:00 in the database. When was it? Was that 1:30 a.m. November 1st PDT, or 1:30 a.m. November 1st PST (an hour later)? You have absolutely no way of knowing. Moral: Always store dates/times in GMT (which doesn't do DST) and convert to the desired timezone as/when necessary.

這篇關于如何獲取 MySQL 的當前時區?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！