如何获取 MySQL 的当前时区? [英] How do I get the current time zone of MySQL?

问题描述

有谁知道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，那么您可以通过 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 的当前时区?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！