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:

  1. mysql> CREATE TABLE tstable (ts TIMESTAMP);
  2. mysql> SET time_zone = 'UTC'; -- insert UTC values
  3. mysql> INSERT INTO tstable VALUES
  4.        ('2018-10-28 00:30:00'),
  5.        ('2018-10-28 01:30:00');
  6. mysql> SELECT ts FROM tstable;
  7. +---------------------+
  8. | ts                  |
  9. +---------------------+
  10. | 2018-10-28 00:30:00 |
  11. | 2018-10-28 01:30:00 |
  12. +---------------------+
  13. mysql> SET time_zone = 'MET'; -- retrieve non-UTC values
  14. mysql> SELECT ts FROM tstable;
  15. +---------------------+
  16. | ts                  |
  17. +---------------------+
  18. | 2018-10-28 02:30:00 |
  19. | 2018-10-28 02:30:00 |
  20. +---------------------+
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.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:

  1. SELECT ts FROM tstable
  2. 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:

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

    1. mysql> SELECT ts FROM tstable
    2.        WHERE ts = '2018-10-28 02:30:00';
    3. +---------------------+
    4. | ts                  |
    5. +---------------------+
    6. | 2018-10-28 02:30:00 |
    7. | 2018-10-28 02:30:00 |
    8. +---------------------+

    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:

    1. mysql> ALTER TABLE tstable ADD INDEX (ts);
    2. mysql> SELECT ts FROM tstable
    3.        WHERE ts = '2018-10-28 02:30:00';
    4. +---------------------+
    5. | ts                  |
    6. +---------------------+
    7. | 2018-10-28 02:30:00 |
    8. +---------------------+

    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:

  1. mysql> SELECT ts FROM tstable
  2.        IGNORE INDEX (ts)
  3.        WHERE ts = '2018-10-28 02:30:00';
  4. +---------------------+
  5. | ts                  |
  6. +---------------------+
  7. | 2018-10-28 02:30:00 |
  8. | 2018-10-28 02:30:00 |
  9. +---------------------+

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


Suchen Sie im MySQL-Handbuch

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

  1. Zeigen Sie - html-Dokument Sprache des Dokuments:en Manuel MySQL : https://dev.mysql.com/

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.

Inhaltsverzeichnis Haut