Rechercher dans le manuel MySQL
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.)
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:
- ('2018-10-28 00:30:00'),
- ('2018-10-28 01:30:00');
- +---------------------+
- | ts |
- +---------------------+
- | 2018-10-28 00:30:00 |
- | 2018-10-28 01:30:00 |
- +---------------------+
- +---------------------+
- | ts |
- +---------------------+
- | 2018-10-28 02:30:00 |
- | 2018-10-28 02:30:00 |
- +---------------------+
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.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.
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:
Suppose further that the query executes under these conditions:
The session time zone is not UTC and has a DST shift. For example:
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):- +---------------------+
- | 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.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:
- +---------------------+
- | 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.
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.
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.
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.
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.
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 |
- +---------------------+
- | 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”.
Document created the 26/06/2006, last modified the 26/10/2018
Source of the printed document:https://www.gaudry.be/en/mysql-rf-timestamp-lookups.html
The infobrol is a personal site whose content is my sole responsibility. The text is available under CreativeCommons license (BY-NC-SA). More info on the terms of use and the author.
References
These references and links indicate documents consulted during the writing of this page, or which may provide additional information, but the authors of these sources can not be held responsible for the content of this page.
The author This site is solely responsible for the way in which the various concepts, and the freedoms that are taken with the reference works, are presented here. Remember that you must cross multiple source information to reduce the risk of errors.