8.3.14 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. (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.)

时间值被以UTC的值存储在TIMESTAMP类型的列上，其插入和查询的值在会话时区和UTC之间进行转换。（可以用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. 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的值在其它时区可能并不是明确的。下面的例子展示了不同的UTC的值，在非UTC时区变成相同的值

mysql> CREATE TABLE tstable (ts TIMESTAMP);
mysql> SET time_zone = 'UTC'; -- insert UTC values
mysql> INSERT INTO tstable VALUES
       ('2018-10-28 00:30:00'),
       ('2018-10-28 01:30:00');
mysql> SELECT ts FROM tstable;
+---------------------+
| ts                  |
+---------------------+
| 2018-10-28 00:30:00 |
| 2018-10-28 01:30:00 |
+---------------------+
mysql> SET time_zone = 'MET'; -- retrieve non-UTC values
mysql> SELECT ts FROM tstable;
+---------------------+
| ts                  |
+---------------------+
| 2018-10-28 02:30:00 |
| 2018-10-28 02:30:00 |
+---------------------+

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.

我们发现两个不同的UTC值，当设置成MET时区时，值变成一样的的了。这个现象导致了，对于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:

假定一个从前面的表中查询ts列的值，例如根据用户提供的时间戳字符串：

SELECT ts FROM tstable
WHERE ts = 'literal';

Suppose further that the query executes under these conditions:

进一步假定在下面的条件下进行查询：

• The session time zone is not UTC and has a DST shift. For example:

     会话时区不是UTC且有夏令时的便宜。比如：

      

SET time_zone = 'MET';

• 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.)

• 唯一的UTC的值村春在TIMESTAMP类型的列上，但是在会话时区由于有夏令时偏移导致不是唯一的值。（上面有已有列子展示了这个影响。）

• 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:

在这些条件下，有索引和没有索引会查询出不同的结果值：

• 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时区转换成会话时区，然后进行比较。

mysql> SELECT ts FROM tstable
       WHERE ts = '2018-10-28 02:30:00';
+---------------------+
| ts                  |
+---------------------+
| 2018-10-28 02:30:00 |
| 2018-10-28 02:30:00 |
+---------------------+

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不同的值转换后却是相同的：一个值是在夏令时偏移前，另一个值在夏令时偏移后。

• 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的索引实例进行比较：

mysql> ALTER TABLE tstable ADD INDEX (ts);
mysql> SELECT ts FROM tstable
       WHERE ts = '2018-10-28 02:30:00';
+---------------------+
| ts                  |
+---------------------+
| 2018-10-28 02:30:00 |
+---------------------+

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.

在前面的讨论中，数据被存储在明确的UTC值的表tstable中。在这些情况下，所有的索引查询都将最多匹配一条索引实例。

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). 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值实例。比如ts列中包含多个UTC值‘2018-10-28 00:30:00’。在这种情况下，查询将返回没一个值（转化成MET值‘2018-10-28 02:30:00’）。仍然是在索引实例中匹配单个索引的值。

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的值匹配成会话时区，变通的方法就是使用IGNORE INDEX命令：

mysql> SELECT ts FROM tstable
       IGNORE INDEX (ts)
       WHERE ts = '2018-10-28 02:30:00';
+---------------------+
| ts                  |
+---------------------+
| 2018-10-28 02:30:00 |
| 2018-10-28 02:30:00 |
+---------------------+

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. See Section 12.7, “Date and Time Functions”.

同样缺少一对一双向转化的函数有 FROM_UNIXTIME() 和UNIX_TIMESTAMP() ，详情请见Section 12.7, “Date and Time Functions”.