content_image/801486.jpg content_image/801487.jpg
(’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 | +---------------------+ 

<img src='content_image/801486.jpg'>

<img src='content_image/801487.jpg'>

## Note 

To use named time zones such as ’MET’  or ’Europe/Amsterdam’ , the time zone tables must be properly set up. For instructions, see Section 5.1.15, “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. 

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: 

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: 

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

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

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 

