MySQL 8.0 Reference Manual Including MySQL NDB Cluster 8.0
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:
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 | +---------------------+
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 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:
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 | +---------------------+
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:
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 | +---------------------+
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”.