如何获取 MySQL 的当前时区？

How do I get the current time zone of MySQL?

提问by user198729

Anyone knows if there is such a function in MySQL?

有谁知道MySQL中是否有这样的功能？

UPDATE

更新

This doesn't output any valid info:

这不会输出任何有效信息：

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

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

或者也许 MySQL 本身不能确切地知道所time_zone使用的，那很好，我们可以PHP在这里参与，只要我能得到有效的信息，而不是SYSTEM......

回答by T.J. Crowder

From the manual (section 9.6):

从手册（第 9.6 节）：

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

可以像这样检索全局和客户端特定时区的当前值：
mysql> SELECT @@global.time_zone, @@session.time_zone;

EditThe above returns SYSTEMif MySQL is set to slave to 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'susing 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 hugeleap.) 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.

编辑SYSTEM如果 MySQL 设置为从属系统时区，则上面返回，这没有帮助。由于您使用的是 PHP，如果 MySQL 的答案是SYSTEM，那么您可以通过 询问系统它使用的时区date_default_timezone_get。（当然，作为VolkerK指出，PHP可以在不同的服务器上运行，但假设去，假设Web服务器和它的谈话是在DB服务器设置为[如果不是实际上在]相同的时区不是一个巨大的飞跃。）但请注意（与 MySQL 一样），您可以设置 PHP 使用的时区（date_default_timezone_set)，这意味着它报告的值可能与操作系统使用的值不同。如果您可以控制 PHP 代码，您应该知道您是否正在这样做并且没问题。

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 nothingabout the data in the database. Read on for details:

但是 MySQL 服务器使用的时区的整个问题可能是一个切线，因为询问服务器它所在的时区并不能告诉您有关数据库中数据的任何信息。请继续阅读以了解详细信息：

Further discussion:

进一步讨论：

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 connectionlike this:

如果您控制服务器，当然可以确保时区是已知数量。如果您无法控制服务器，则可以像这样设置连接使用的时区：

set time_zone = '+00:00';

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

这now()会将时区设置为 GMT，以便任何进一步的操作（如）都将使用 GMT。

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

但请注意，时间和日期值不与 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)

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 assumethey 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.

因此，了解服务器的时区是在该获取时间功能方面唯一重要的权利，比如now()，unix_timestamp()等; 它不会告诉您有关数据库数据中日期使用的时区的任何信息。您可能会选择假设它们是使用服务器的时区编写的，但这种假设很可能是有缺陷的。要了解存储在数据中的任何日期或时间的时区，您必须确保它们与时区信息一起存储，或者（就像我一样）确保它们始终在 GMT 中。

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 ifthe 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:00in 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.

为什么假设数据是使用服务器的时区写入的？嗯，一方面，数据可能是使用设置不同时区的连接写入的。数据库可能已从一台服务器移动到另一台服务器，其中服务器位于不同的时区（当我继承从德克萨斯州移动到加利福尼亚州的数据库时遇到了这种情况）。但是即使数据写在服务器上，使用它的当前时区，它仍然是不明确的。去年，在美国，夏令时在 11 月 1 日凌晨 2:00 关闭。假设我的服务器在使用太平洋时区的加利福尼亚州，并且我有值2009-11-01 01:30:00在数据库中。那是什么时候？那是太平洋标准时间 11 月 1 日凌晨 1 点 30 分，还是太平洋标准时间 11 月 1 日凌晨 1 点 30 分（一小时后）？你完全没有办法知道。道德：始终以格林威治标准时间（不执行夏令时）存储日期/时间，并在必要时转换为所需的时区。

回答by JohnZ

The query below returns the timezone of the current session.

下面的查询返回当前会话的时区。

select timediff(now(),convert_tz(now(),@@session.time_zone,'+00:00'));

回答by Andrew

Simply SELECT @@system_time_zone;

简单地 SELECT @@system_time_zone;

Returns PST(or whatever is relevant to your system).

返回PST（或与您的系统相关的任何内容）。

If you're trying to determine the session timezone you can use this query:
SELECT IF(@@session.time_zone = 'SYSTEM', @@system_time_zone, @@session.time_zone);

如果您尝试确定会话时区，您可以使用以下查询：
SELECT IF(@@session.time_zone = 'SYSTEM', @@system_time_zone, @@session.time_zone);

Which will return the session timezone if it differs from the system timezone.

如果会话时区与系统时区不同，它将返回会话时区。

回答by Timo Huovinen

As Jakub Vrána(The creator or Adminerand NotORM) mentions in the comments, to select the current timezone offset in TIMEuse:

正如Jakub Vrána（创建者或Adminer和NotORM）在评论中提到的，选择当前TIME使用的时区偏移：

SELECT TIMEDIFF(NOW(), UTC_TIMESTAMP);

It will return: 02:00:00if your timezone is +2:00 for that date

它将返回：02:00:00如果您的时区是该日期的 +2:00

I made a cheatsheet here: Should MySQL have its timezone set to UTC?

我在这里做了一个备忘单：MySQL 是否应该将时区设置为 UTC？

回答by Abhinav bhardwaj

To get Current timezone of the mysql you can do following things:

要获取 mysql 的当前时区，您可以执行以下操作：

 1. SELECT @@system_time_zone;   //from this you can get the system timezone 
 2. SELECT IF(@@session.time_zone = 'SYSTEM', @@system_time_zone, @@session.time_zone) //This will give you time zone if system timezone is different from global timezone

Now if you want to change the mysql timezone then:

现在，如果您想更改 mysql 时区，则：

 1. SET GLOBAL time_zone = '+00:00'   //this will set mysql timezone in UTC
 2. SET @@session.time_zone = "+00:00";  //by this you can chnage the timezone only for your particular session 

回答by Luca Fagioli

SELECT EXTRACT(HOUR FROM (TIMEDIFF(NOW(), UTC_TIMESTAMP))) AS `timezone`

This will return the timezone as an integer (eg: -6), handling positive or negative times (here is where EXTRACTcomes into play: HOURfunction alone returns negative timezones as positive).

这会将时区作为整数返回（例如：）-6，处理正或负时间（这里是EXTRACT发挥HOUR作用的地方：单独的函数将负时区返回为正）。

回答by Amitesh

The command mention in the description returns "SYSTEM" which indicated it takes the timezone of the server. Which is not useful for our query.

描述中提到的命令返回“SYSTEM”，表示它采用服务器的时区。这对我们的查询没有用。

Following query will help to understand the timezone

以下查询将有助于了解时区

SELECT TIMEDIFF(NOW(), UTC_TIMESTAMP) as GMT_TIME_DIFF;

Above query will give you the time interval with respect to Coordinated Universal Time(UTC). So you can easily analyze the timezone. if the database time zone is IST the output will be 5:30

以上查询将为您提供与协调世界时 (UTC) 相关的时间间隔。因此，您可以轻松分析时区。如果数据库时区是 IST，则输出将为 5:30

UTC_TIMESTAMP

UTC_TIMESTAMP

In MySQL, the UTC_TIMESTAMP returns the current UTC date and time as a value in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS.uuuuuu format depending on the usage of the function i.e. in a string or numeric context.

在 MySQL 中，UTC_TIMESTAMP 返回当前 UTC 日期和时间作为 'YYYY-MM-DD HH:MM:SS' 或 YYYYMMDDHHMMSS.uuuuuu 格式的值，具体取决于函数的用法，即在字符串或数字上下文中。

NOW()

现在（）

NOW() function. MySQL NOW() returns the value of current date and time in 'YYYY-MM-DD HH:MM:SS' format or YYYYMMDDHHMMSS.uuuuuu format depending on the context (numeric or string) of the function. CURRENT_TIMESTAMP, CURRENT_TIMESTAMP(), LOCALTIME, LOCALTIME(), LOCALTIMESTAMP, LOCALTIMESTAMP() are synonyms of NOW().

现在（）函数。MySQL NOW() 根据函数的上下文（数字或字符串）以 'YYYY-MM-DD HH:MM:SS' 格式或 YYYYMMDDHHMMSS.uuuuuu 格式返回当前日期和时间的值。CURRENT_TIMESTAMP、CURRENT_TIMESTAMP()、LOCALTIME、LOCALTIME()、LOCALTIMESTAMP、LOCALTIMESTAMP() 是 NOW() 的同义词。

回答by ttrasn

To anyone come to find timezone of mysql db.

任何人都可以找到 mysql db 的时区。

With this query you can get current timezone :

通过此查询，您可以获得当前时区：

mysql> SELECT @@system_time_zone as tz;
+-------+
|  tz   |
+-------+
|  CET  |
+-------+

回答by Pekka

Check out MySQL Server Time Zone Supportand the system_time_zonesystem variable. Does that help?

查看MySQL 服务器时区支持和system_time_zone系统变量。这有帮助吗？

回答by vladkras

My PHP framework uses

我的 PHP 框架使用

SET LOCAL time_zone='Whatever'

on after connect, where 'Whatever' == date_default_timezone_get()

连接后，其中 'Whatever' == date_default_timezone_get()

Not my solution, but this ensures SYSTEMtimezone of MySQL server is always the same as PHP's one

不是我的解决方案，但这可确保SYSTEMMySQL 服务器的时区始终与 PHP 的时区相同

So, yes, PHP is strongly envolved and can affect it

所以，是的，PHP 被强烈地包围并且可以影响它