このページは機械翻訳したものです。

8.3.14 TIMESTAMP カラムからのインデックス付きルックアップ

一時値は UTC 値として TIMESTAMP カラムに格納され、TIMESTAMP カラムに対して挿入および取得された値はセッションタイムゾーンと UTC の間で変換されます。 (これは、CONVERT_TZ() 関数によって実行される変換と同じタイプです。 セッションのタイムゾーンが UTC の場合、事実上タイムゾーンの変換は行われません。)

夏時間 (DST) などのローカルタイムゾーンの変更の規則により、UTC タイムゾーンと UTC 以外のタイムゾーン間の変換は両方向で 1 対 1 ではありません。 個別の 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 |
+---------------------+

注記

'MET'や'Europe/Amsterdam'などの名前付きタイムゾーンを使用するには、タイムゾーンテーブルが適切に設定されている必要があります。 その手順は、セクション5.1.15「MySQL Server でのタイムゾーンのサポート」を参照してください。

'MET'タイムゾーンに変換すると、2 つの異なる UTC 値が同じであることがわかります。 この現象は、オプティマイザがインデックスを使用してクエリーを実行するかどうかに応じて、特定の TIMESTAMP カラムクエリーの結果が異なる可能性があります。

クエリーで、WHERE 句を使用して ts カラムでユーザー指定のタイムスタンプリテラルなどの単一の特定の値を検索するために、前述のテーブルから値を選択するとします:

SELECT ts FROM tstable
WHERE ts = 'literal';

さらに、クエリーが次の条件下で実行されるとします:

• セッションのタイムゾーンは UTC ではなく、DST シフトがあります。 例:

  SET time_zone = 'MET';

• DST シフトのため、TIMESTAMP カラムに格納されている一意の UTC 値がセッションタイムゾーンで一意ではありません。 (前述の例は、これがどのように発生するかを示しています。)

• クエリーでは、セッションタイムゾーンの DST への入力時間内の検索値を指定します。

これらの条件では、インデックス付けされていない参照とインデックス付けされた参照で WHERE 句の比較が様々な方法で行われ、結果が異なります:

• インデックスがない場合、またはオプティマイザがインデックスを使用できない場合は、セッションのタイムゾーンで比較が行われます。 オプティマイザは、各 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 |
  +---------------------+

  格納された ts 値はセッションタイムゾーンに変換されるため、UTC 値とは異なるが、セッションタイムゾーンでは等しい 2 つのタイムスタンプ値をクエリーで返すことができます: クロックが変更されたときに DST シフトの前に発生する値と、DST シフトの後に発生した値。

• 使用可能なインデックスがある場合、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 |
  +---------------------+

  この場合、(変換された) 検索値はインデックスエントリにのみ一致し、格納されている個別の UTC 値のインデックスエントリも個別であるため、検索値はそれらのいずれかにのみ一致できます。

インデックス付けされていないルックアップとインデックス付けされたルックアップのオプティマイザ操作が異なるため、クエリーではそれぞれ異なる結果が生成されます。 インデックス付けされていない参照の結果は、セッションタイムゾーンで一致するすべての値を返します。 インデックス付きルックアップではこれを実行できません:

• UTC 値についてのみ認識されるストレージエンジン内で実行されます。

• 同じ UTC 値にマップされる 2 つの異なるセッションタイムゾーン値の場合、インデックス付き参照は対応する UTC インデックスエントリのみに一致し、単一行のみを返します。

前述の説明では、tstable に格納されているデータセットは個別の UTC 値で構成されています。 このような場合、表示された形式のすべてのインデックス使用クエリーは、最大 1 つのインデックスエントリに一致します。

インデックスが UNIQUE でない場合は、テーブル (およびインデックス) に特定の UTC 値の複数のインスタンスを格納できます。 たとえば、ts カラムに UTC 値'2018-10-28 00:30:00'の複数のインスタンスが含まれる場合があります。 この場合、インデックス使用クエリーはそれぞれを戻します (結果セットで MET 値'2018-10-28 02:30:00'に変換されます)。 インデックス使用クエリーは、セッションタイムゾーンの検索値に変換される複数の UTC 値を照合するのではなく、変換された検索値を UTC インデックスエントリの単一の値に一致させることができます。

セッションタイムゾーンに一致するすべての 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 |
+---------------------+

FROM_UNIXTIME() 関数や UNIX_TIMESTAMP() 関数で実行される変換など、両方の方向でのタイムゾーン変換に同じ一対一マッピングがないことが他のコンテキストでも発生します。 セクション12.7「日付および時間関数」を参照してください。