MySQL 5.7-8.3.11 Indexed Lookups from TIMESTAMP Columns

Temporal values are stored in TIMESTAMP columns as UTC values, and values inserted into and retrieved from TIMESTAMP columns are converted between the session time zone and UTC.

时间值作为UTC值存储在TIMESTAMP列中，插入到TIMESTAMP列中的值和从TIMESTAMP列中检索的值在会话时区和UTC之间进行转换。
 

(This is the same type of conversion performed by the CONVERT_TZ() function. If the session time zone is UTC, there is effectively no time zone conversion.)

(这与CONVERT_TZ()函数执行的转换类型相同。如果会话时区为UTC，则实际上没有时区转换。)

Due to conventions for local time zone changes such as Daylight Saving Time (DST), conversions between UTC and non-UTC time zones are not one-to-one in both directions.

由于当地时区变化的约定，如夏令时(DST)， UTC和非UTC时区之间的转换不是在两个方向上一对一的。

UTC values that are distinct may not be distinct in another time zone. The following example shows distinct UTC values that become identical in a non-UTC time zone:

不同的UTC值在另一个时区中可能不不同。下面的示例显示了在非UTC时区中变得相同的不同UTC值:

 

Note

To use named time zones such as 'MET' or 'Europe/Amsterdam', the time zone tables must be properly set up.

要使用“MET”或“Europe/Amsterdam”等命名时区，必须正确设置时区表。

For instructions, see Section 5.1.13, “MySQL Server Time Zone Support”.

You can see that the two distinct UTC values are the same when converted to the 'MET' time zone. This phenomenon can lead to different results for a given TIMESTAMP column query, depending on whether the optimizer uses an index to execute the query.

您可以看到，当转换为“MET”时区时，两个不同的UTC值是相同的。对于给定的TIMESTAMP列查询，这种现象可能导致不同的结果，这取决于优化器是否使用索引来执行查询。

Suppose that a query selects values from the table shown earlier using a WHERE clause to search the ts column for a single specific value such as a user-provided timestamp literal:

假设查询使用WHERE子句从前面显示的表中选择值，以在ts列中搜索单个特定值，如用户提供的时间戳文字:

 

Suppose further that the query executes under these conditions:

进一步假设查询在以下条件下执行:

• The session time zone is not UTC and has a DST shift. For example:
• 会话时区不是UTC，并且有DST偏移。例如:

 

• Unique UTC values stored in the TIMESTAMP column are not unique in the session time zone due to DST shifts. (The example shown earlier illustrates how this can occur.)

• 由于DST的变化，TIMESTAMP列中存储的唯一UTC值在会话时区中不是唯一的。(前面展示的示例说明了这是如何发生的。)

• The query specifies a search value that is within the hour of entry into DST in the session time zone.

• 查询指定在会话时区进入夏令时的小时内的搜索值。

Under those conditions, the comparison in the WHERE clause occurs in different ways for nonindexed and indexed lookups and leads to different results:

在这些条件下，对于非索引和索引查找，WHERE子句中的比较以不同的方式发生，并导致不同的结果:

• If there is no index or the optimizer cannot use it, comparisons occur in the session time zone. The optimizer performs a table scan in which it retrieves each ts column value, converts it from UTC to the session time zone, and compares it to the search value (also interpreted in the session time zone):
• 如果没有索引或优化器不能使用它，则在会话时区中进行比较。优化器执行一次表扫描，检索每个ts列值，将其从UTC转换为会话时区，并将其与搜索值(也在会话时区中解释)进行比较:

Because the stored ts values are converted to the session time zone, it is possible for the query to return two timestamp values that are distinct as UTC values but equal in the session time zone: One value that occurs before the DST shift when clocks are changed, and one value that was occurs after the DST shift. 

因为ts值转换为存储会话时区,它查询可以返回两个截然不同的是UTC时间戳值值但平等在会话时区:一个值发生在DST移位时钟改变时,和一个值在DST后发生转变。

If there is a usable index, comparisons occur in UTC. The optimizer performs an index scan, first converting the search value from the session time zone to UTC, then comparing the result to the UTC index entries:

如果有可用的索引，则使用UTC进行比较。优化器执行索引扫描，首先将搜索值从会话时区转换为UTC，然后将结果与UTC索引项进行比较:

In this case, the (converted) search value is matched only to index entries, and because the index entries for the distinct stored UTC values are also distinct, the search value can match only one of them. 

在本例中，(转换后的)搜索值只与索引项匹配，因为不同存储的UTC值的索引项也是不同的，所以搜索值只能匹配其中一个。

Due to different optimizer operation for nonindexed and indexed lookups, the query produces different results in each case. The result from the nonindexed lookup returns all values that match in the session time zone. The indexed lookup cannot do so:

由于针对非索引和索引查找的优化器操作不同，查询在每种情况下都会产生不同的结果。非索引查找的结果返回会话时区中匹配的所有值。索引查找不能这样做:

• It is performed within the storage engine, which knows only about UTC values.

• 是在存储引擎中执行的，存储引擎只知道UTC值。

• For the two distinct session time zone values that map to the same UTC value, the indexed lookup matches only the corresponding UTC index entry and returns only a single row.

• 对于映射到相同UTC值的两个不同的会话时区值，索引查找只匹配相应的UTC索引条目，并且只返回单个行。

In the preceding discussion, the data set stored in tstable happens to consist of distinct UTC values. In such cases, all index-using queries of the form shown match at most one index entry.

在前面的讨论中，存储在tstable中的数据集恰好由不同的UTC值组成。在这种情况下，所显示的表单的所有使用索引的查询最多只匹配一个索引条目。

If the index is not UNIQUE, it is possible for the table (and the index) to store multiple instances of a given UTC value. For example, the ts column might contain multiple instances of the UTC value '2018-10-28 00:30:00'. In this case, the index-using query would return each of them (converted to the MET value '2018-10-28 02:30:00' in the result set).

如果索引不是UNIQUE，则表(和索引)可以存储给定UTC值的多个实例。例如，ts列可能包含多个UTC值'2018-10-28 00:30:00'的实例。在本例中，使用索引的查询将返回它们中的每一个(转换为结果集中的MET值'2018-10-28 02:30:00')。

It remains true that index-using queries match the converted search value to a single value in the UTC index entries, rather than matching multiple UTC values that convert to the search value in the session time zone.

使用索引的查询将转换后的搜索值匹配到UTC索引项中的单个值，而不是匹配多个转换为会话时区中的搜索值的UTC值。

If it is important to return all ts values that match in the session time zone, the workaround is to suppress use of the index with an IGNORE INDEX hint:

如果返回会话时区中匹配的所有ts值很重要，那么解决方案是使用忽略索引提示来禁止使用索引:

The same lack of one-to-one mapping for time zone conversions in both directions occurs in other contexts as well, such as conversions performed with the FROM_UNIXTIME() and UNIX_TIMESTAMP() functions.

在其他上下文中也会出现同样的情况，比如使用FROM_UNIXTIME()和UNIX_TIMESTAMP()函数执行的时区转换。
​

See Section 12.7, “Date and Time Functions”.