安装介绍路径

MySQL Server Time Zone Support 5.1.13

This section describes the time zone settings maintained by MySQL, how to load the system tables required for named time support, how to stay current with time zone changes, and how to enable leap-second support.

本节介绍了MySQL维护的时区设置，如何加载命名时间支持所需的系统表，如何在时区更改时保持最新状态以及如何启用leap秒支持。

MySQL Server maintains several time zone settings:

The system time zone. When the server starts, it attempts to determine the time zone of the host machine automatically and uses it to set the system variable. The value does not change thereafter.

To explicitly specify the system time zone for MySQL Server at startup, set the TZ environment variable before you start . If you start the server using , its option provides another way to set the system time zone. The permissible values for TZ and are system dependent. Consult your operating system documentation to see what values are acceptable.

The server current time zone. The global system variable indicates the time zone the server currently is operating in. The initial value is 'SYSTEM', which indicates that the server time zone is the same as the system time zone.

MySQL Server维护几个时区设置：

系统时区。服务器启动时，它将尝试自动确定主机的时区，并使用它来设置system_time_zone系统变量。此后该值不变。

要在启动时为MySQL Server明确指定系统时区，请在启动mysqld之前设置TZ环境变量。如果使用mysqld_safe启动服务器，则其--timezone选项提供了另一种设置系统时区的方法。

TZ和--timezone的允许值取决于系统。请查阅操作系统文档，以了解可以接受的值。

服务器当前时区。global time_zone系统变量指示服务器当前正在运行的时区。time_zone的初始值为'SYSTEM'，表示服务器时区与系统时区相同。

Note

If set to SYSTEM, every MySQL function call that requires a time zone calculation makes a system library call to determine the current system time zone. This call may be protected by a global mutex, resulting in contention.

注意：如果设置为SYSTEM，则每个需要时区计算的MySQL函数调用都会进行系统库调用，以确定当前系统时区。该调用可能受到全局互斥锁的保护，从而导致争用。

The initial global server time zone value can be specified explicitly at startup with the --default-time-zone option on the command line, or you can use the following line in an option file:

初始全局服务器时区值可以在启动时通过命令行上的--default-time-zone选项显式指定，也可以在选项文件中使用以下行：

default-time-zone='timezone'

If you have the SUPER privilege, you can set the global server time zone value at runtime with this statement:

如果您具有SUPER特权，则可以使用以下语句在运行时设置全局服务器时区值：

SET GLOBAL time_zone = timezone;

Per-session time zones. Each client that connects has its own session time zone setting, given by the session time_zone variable. Initially, the session variable takes its value from the global time_zone variable, but the client can change its own time zone with this statement:

SET time_zone = timezone;

The session time zone setting affects display and storage of time values that are zone-sensitive. This includes the values displayed by functions such as NOW() or CURTIME(), and values stored in and retrieved from TIMESTAMP columns. Values for TIMESTAMP columns are converted from the session time zone to UTC for storage, and from UTC to the session time zone for retrieval.

The session time zone setting does not affect values displayed by functions such as UTC_TIMESTAMP() or values in DATE, TIME, or DATETIME columns. Nor are values in those data types stored in UTC; the time zone applies for them only when converting from TIMESTAMP values. If you want locale-specific arithmetic for DATE, TIME, or DATETIME values, convert them to UTC, perform the arithmetic, and then convert back.

会话时区设置会影响对时区敏感的时间值的显示和存储。这包括由NOW（）或CURTIME（）之类的函数显示的值，以及在TIMESTAMP列中存储和检索的值。 TIMESTAMP列的值从会话时区转换为UTC以进行存储，并从UTC转换为会话时区以进行检索。

会话时区设置不影响UTC_TIMESTAMP（）之类的函数显示的值或DATE，TIME或DATETIME列中的值。这些数据类型中的值也不会存储在UTC中；仅当从TIMESTAMP值转换时，时区才适用于它们。如果要使用DATE，TIME或DATETIME值的特定于语言环境的算术，请将它们转换为UTC，执行算术，然后再转换回去。

The current global and session time zone values can be retrieved like this:

SELECT @@GLOBAL.time_zone, @@SESSION.time_zone;

timezone values can be given in several formats, none of which are case-sensitive:

As the value 'SYSTEM', indicating that the server time zone is the same as the system time zone.

As a string indicating an offset from UTC of the form [H]H:MM, prefixed with a + or -, such as '+10:00', '-6:00', or '+05:30'. A leading zero can optionally be used for hours values less than 10; MySQL prepends a leading zero when storing and retriving the value in such cases. MySQL converts '-00:00' or '-0:00' to '+00:00'.

A time zone offset must be in the range '-12:59' to '+13:00', inclusive.

As a named time zone, such as 'Europe/Helsinki', 'US/Eastern', 'MET', or 'UTC'.

时区值可以多种格式给出，都不区分大小写：

作为值“ SYSTEM”，表示服务器时区与系统时区相同。

作为字符串，以[H] H：MM形式表示与UTC的偏移量，以+或-开头，例如'+10：00'，'-6:00'或'+05：30'。前导零可用于小于10的小时值；在这种情况下，MySQL在存储和检索该值时会加上前导零。 MySQL将'-00：00'或'-0：00'转换为'+00：00'。

时区偏移量必须在“ -12：59”到“ +13：00”之间（包括该范围）。

作为命名时区，例如“Europe/Helsinki”，“US/Eastern”，“ MET”或“ UTC”。

Note

Named time zones can be used only if the time zone information tables in the mysql database have been created and populated. Otherwise, use of a named time zone results in an error:

仅当已创建并填充了mysql数据库中的时区信息表时，才能使用命名时区

mysql> SET time_zone = 'UTC'; ERROR 1298 (HY000): Unknown or incorrect time zone: 'UTC'

参照

Staying Current with Time Zone Changes

When time zone rules change, applications that use the old rules become out of date. To stay current, it is necessary to make sure that your system uses current time zone information is used. For MySQL, there are multiple factors to consider in staying current:

当时区规则更改时，使用旧规则的应用程序将过时。要保持最新状态，必须确保系统使用了当前时区信息。对于MySQL，保持最新状态需要考虑多个因素：

The operating system time affects the value that the MySQL server uses for times if its time zone is set to SYSTEM. Make sure that your operating system is using the latest time zone information. For most operating systems, the latest update or service pack prepares your system for the time changes. Check the website for your operating system vendor for an update that addresses the time changes.

如果不确定命名时区是否可用，用作服务器的时区设置还是由设置自己的时区的客户端使用，请检查时区表是否为空。以下查询确定包含时区名称的表是否具有任何行：

If you replace the system's /etc/localtime time zone file with a version that uses rules differing from those in effect at startup, restart so that it uses the updated rules. Otherwise, might not notice when the system changes its time.

如果将系统的/ etc / localtime时区文件替换为使用与mysqld启动时生效的规则不同的规则的版本，请重新启动mysqld，以使其使用更新后的规则。否则，mysqld可能不会注意到系统何时更改其时间。

If you use named time zones with MySQL, make sure that the time zone tables in the mysql database are up to date:

如果使用MySQL中时区，请确保mysql数据库中的时区表是最新的：

If your system has its own zoneinfo database, reload the MySQL time zone tables whenever the zoneinfo database is updated.

如果您的系统有自己的zoneinfo数据库，则在更新zoneinfo数据库时，请重新加载MySQL时区表。

For systems that do not have their own zoneinfo database, check the MySQL Developer Zone for updates. When a new update is available, download it and use it to replace the content of your current time zone tables.

对于没有自己的zoneinfo数据库的系统，请检查MySQL开发人员区域以获取更新。当有新更新可用时，请下载并使用它来替换当前时区表的内容。

If you are uncertain whether named time zones are available, for use either as the server's time zone setting or by clients that set their own time zone, check whether your time zone tables are empty. The following query determines whether the table that contains time zone names has any rows:

如果不确定命名时区是否可用，用作服务器的时区设置还是由设置自己的时区的客户端使用，请检查时区表是否为空。以下查询确定包含时区名称的表是否具有任何行：

mysql> SELECT COUNT(*) FROM mysql.time_zone_name;

A count of zero indicates that the table is empty. In this case, no applications currently are using named time zones, and you need not update the tables (unless you want to enable named time zone support). A count greater than zero indicates that the table is not empty and that its contents are available to be used for named time zone support. In this case, be sure to reload your time zone tables so that applications that use named time zones obtain correct query results.

To check whether your MySQL installation is updated properly for a change in Daylight Saving Time rules, use a test like the one following. The example uses values that are appropriate for the 2007 DST 1-hour change that occurs in the United States on March 11 at 2 a.m.

计数为零表示该表为空。在这种情况下，当前没有应用程序使用命名时区，并且您不需要更新表（除非您要启用命名时区支持）。大于零的计数表示该表不为空，并且其内容可用于命名时区支持。在这种情况下，请确保重新加载时区表，以便使用命名时区的应用程序获得正确的查询结果。

要检查您的MySQL安装是否已正确更新以适应夏令时规则，请使用以下测试。该示例使用的值适用于3月11日凌晨2点在美国发生的2007 DST 1小时更改。

The test uses this query:

The two time values indicate the times at which the DST change occurs, and the use of named time zones requires that the time zone tables be used. The desired result is that both queries return the same result (the input time, converted to the equivalent value in the 'US/Central' time zone).

这两个时间值指示DST更改发生的时间，并且使用命名时区要求使用时区表。理想的结果是两个查询都返回相同的结果（输入时间，在“US/Central”时区中转换为等效值）。

Before updating the time zone tables, you see an incorrect result like this:

+---------------------+---------------------+| time1               | time2               |+---------------------+---------------------+| 2007-03-11 01:00:00 | 2007-03-11 02:00:00 |+---------------------+---------------------+

After updating the tables, you should see the correct result:

+---------------------+---------------------+| time1               | time2               |+---------------------+---------------------+| 2007-03-11 01:00:00 | 2007-03-11 01:00:00 |+---------------------+---------------------+

转载地址：http://ihxwb.baihongyu.com/

你可能感兴趣的文章