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”.
Deutsche Übersetzung
Sie haben gebeten, diese Seite auf Deutsch zu besuchen. Momentan ist nur die Oberfläche übersetzt, aber noch nicht der gesamte Inhalt.Wenn Sie mir bei Übersetzungen helfen wollen, ist Ihr Beitrag willkommen. Alles, was Sie tun müssen, ist, sich auf der Website zu registrieren und mir eine Nachricht zu schicken, in der Sie gebeten werden, Sie der Gruppe der Übersetzer hinzuzufügen, die Ihnen die Möglichkeit gibt, die gewünschten Seiten zu übersetzen. Ein Link am Ende jeder übersetzten Seite zeigt an, dass Sie der Übersetzer sind und einen Link zu Ihrem Profil haben.
Vielen Dank im Voraus.
Dokument erstellt 26/06/2006, zuletzt geändert 26/10/2018
Quelle des gedruckten Dokuments:https://www.gaudry.be/de/mysql-rf-timestamp-lookups.html
Die Infobro ist eine persönliche Seite, deren Inhalt in meiner alleinigen Verantwortung liegt. Der Text ist unter der CreativeCommons-Lizenz (BY-NC-SA) verfügbar. Weitere Informationen auf die Nutzungsbedingungen und dem Autor.
Referenzen
Diese Verweise und Links verweisen auf Dokumente, die während des Schreibens dieser Seite konsultiert wurden, oder die zusätzliche Informationen liefern können, aber die Autoren dieser Quellen können nicht für den Inhalt dieser Seite verantwortlich gemacht werden.
Der Autor Diese Website ist allein dafür verantwortlich, wie die verschiedenen Konzepte und Freiheiten, die mit den Nachschlagewerken gemacht werden, hier dargestellt werden. Denken Sie daran, dass Sie mehrere Quellinformationen austauschen müssen, um das Risiko von Fehlern zu reduzieren.