-------------- drop table if exists ts -------------- Query OK, 0 rows affected, 1 warning (0.02 sec) -------------- select "Using time_zone as UTC, to be 'safe' from local time conversion (conversions may still be done but only within UTC)" as 'STATUS' -------------- +---------------------------------------------------------------------------------------------------------------------+ | STATUS | +---------------------------------------------------------------------------------------------------------------------+ | Using time_zone as UTC, to be 'safe' from local time conversion (conversions may still be done but only within UTC) | +---------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) -------------- set time_zone = '+00:00' -------------- Query OK, 0 rows affected (0.00 sec) -------------- select @@version, @@time_zone -------------- +-----------+-------------+ | @@version | @@time_zone | +-----------+-------------+ | 8.0.22 | +00:00 | +-----------+-------------+ 1 row in set (0.00 sec) -------------- create table ts (id int unsigned not null auto_increment primary key, ts timestamp) -------------- Query OK, 0 rows affected (0.02 sec) -------------- insert into ts (ts) values ('2020-10-24 23:40:00'), ('2020-10-25 00:00:00'), ('2020-10-25 00:20:00'), ('2020-10-25 00:40:00'), ('2020-10-25 01:00:00'), ('2020-10-25 01:20:00'), ('2020-10-25 01:40:00'), ('2020-10-25 02:00:00'), ('2020-10-25 02:20:00') -------------- Query OK, 9 rows affected (0.00 sec) Records: 9 Duplicates: 0 Warnings: 0 -------------- show create table ts -------------- +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ts | CREATE TABLE `ts` ( `id` int unsigned NOT NULL AUTO_INCREMENT, `ts` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci | +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) -------------- select *, unix_timestamp(ts), cast(ts AT TIME ZONE '+00:00' AS DATETIME) as `utc_time` from ts -------------- +----+---------------------+--------------------+---------------------+ | id | ts | unix_timestamp(ts) | utc_time | +----+---------------------+--------------------+---------------------+ | 1 | 2020-10-24 23:40:00 | 1603582800 | 2020-10-24 23:40:00 | | 2 | 2020-10-25 00:00:00 | 1603584000 | 2020-10-25 00:00:00 | | 3 | 2020-10-25 00:20:00 | 1603585200 | 2020-10-25 00:20:00 | | 4 | 2020-10-25 00:40:00 | 1603586400 | 2020-10-25 00:40:00 | | 5 | 2020-10-25 01:00:00 | 1603587600 | 2020-10-25 01:00:00 | | 6 | 2020-10-25 01:20:00 | 1603588800 | 2020-10-25 01:20:00 | | 7 | 2020-10-25 01:40:00 | 1603590000 | 2020-10-25 01:40:00 | | 8 | 2020-10-25 02:00:00 | 1603591200 | 2020-10-25 02:00:00 | | 9 | 2020-10-25 02:20:00 | 1603592400 | 2020-10-25 02:20:00 | +----+---------------------+--------------------+---------------------+ 9 rows in set (0.00 sec) -------------- select *, unix_timestamp(ts), cast(ts AT TIME ZONE '+00:00' AS DATETIME) as `utc_time` from ts where ts > '2020-10-25 02:20:00+01:00' -------------- +----+---------------------+--------------------+---------------------+ | id | ts | unix_timestamp(ts) | utc_time | +----+---------------------+--------------------+---------------------+ | 7 | 2020-10-25 01:40:00 | 1603590000 | 2020-10-25 01:40:00 | | 8 | 2020-10-25 02:00:00 | 1603591200 | 2020-10-25 02:00:00 | | 9 | 2020-10-25 02:20:00 | 1603592400 | 2020-10-25 02:20:00 | +----+---------------------+--------------------+---------------------+ 3 rows in set (0.00 sec) -------------- select *, unix_timestamp(ts), cast(ts AT TIME ZONE '+00:00' AS DATETIME) as `utc_time` from ts where ts > '2020-10-25 02:20:00+02:00' -------------- +----+---------------------+--------------------+---------------------+ | id | ts | unix_timestamp(ts) | utc_time | +----+---------------------+--------------------+---------------------+ | 4 | 2020-10-25 00:40:00 | 1603586400 | 2020-10-25 00:40:00 | | 5 | 2020-10-25 01:00:00 | 1603587600 | 2020-10-25 01:00:00 | | 6 | 2020-10-25 01:20:00 | 1603588800 | 2020-10-25 01:20:00 | | 7 | 2020-10-25 01:40:00 | 1603590000 | 2020-10-25 01:40:00 | | 8 | 2020-10-25 02:00:00 | 1603591200 | 2020-10-25 02:00:00 | | 9 | 2020-10-25 02:20:00 | 1603592400 | 2020-10-25 02:20:00 | +----+---------------------+--------------------+---------------------+ 6 rows in set (0.00 sec) -------------- explain select *, unix_timestamp(ts), cast(ts AT TIME ZONE '+00:00' AS DATETIME) as `utc_time` from ts where ts > '2020-10-25 02:20:00+01:00' -------------- +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | ts | NULL | ALL | NULL | NULL | NULL | NULL | 9 | 33.33 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) -------------- show warnings -------------- +-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Note | 1003 | /* select#1 */ select `mysql`.`ts`.`id` AS `id`,`mysql`.`ts`.`ts` AS `ts`,unix_timestamp(`mysql`.`ts`.`ts`) AS `unix_timestamp(ts)`,cast(time_zone(`mysql`.`ts`.`ts`) as datetime) AS `utc_time` from `mysql`.`ts` where (`mysql`.`ts`.`ts` > TIMESTAMP'2020-10-25 01:20:00') | +-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) -------------- explain select *, unix_timestamp(ts), cast(ts AT TIME ZONE '+00:00' AS DATETIME) as `utc_time` from ts where ts > '2020-10-25 02:20:00+02:00' -------------- +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | ts | NULL | ALL | NULL | NULL | NULL | NULL | 9 | 33.33 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) -------------- show warnings -------------- +-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Note | 1003 | /* select#1 */ select `mysql`.`ts`.`id` AS `id`,`mysql`.`ts`.`ts` AS `ts`,unix_timestamp(`mysql`.`ts`.`ts`) AS `unix_timestamp(ts)`,cast(time_zone(`mysql`.`ts`.`ts`) as datetime) AS `utc_time` from `mysql`.`ts` where (`mysql`.`ts`.`ts` > TIMESTAMP'2020-10-25 00:20:00') | +-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) -------------- set time_zone = 'CET' -------------- Query OK, 0 rows affected (0.00 sec) -------------- select @@version, @@time_zone -------------- +-----------+-------------+ | @@version | @@time_zone | +-----------+-------------+ | 8.0.22 | CET | +-----------+-------------+ 1 row in set (0.00 sec) -------------- select *, unix_timestamp(ts), cast(ts AT TIME ZONE '+00:00' AS DATETIME) as `utc_time` from ts -------------- +----+---------------------+--------------------+---------------------+ | id | ts | unix_timestamp(ts) | utc_time | +----+---------------------+--------------------+---------------------+ | 1 | 2020-10-25 01:40:00 | 1603582800 | 2020-10-24 23:40:00 | | 2 | 2020-10-25 02:00:00 | 1603584000 | 2020-10-25 00:00:00 | | 3 | 2020-10-25 02:20:00 | 1603585200 | 2020-10-25 00:20:00 | | 4 | 2020-10-25 02:40:00 | 1603586400 | 2020-10-25 00:40:00 | | 5 | 2020-10-25 02:00:00 | 1603587600 | 2020-10-25 01:00:00 | | 6 | 2020-10-25 02:20:00 | 1603588800 | 2020-10-25 01:20:00 | | 7 | 2020-10-25 02:40:00 | 1603590000 | 2020-10-25 01:40:00 | | 8 | 2020-10-25 03:00:00 | 1603591200 | 2020-10-25 02:00:00 | | 9 | 2020-10-25 03:20:00 | 1603592400 | 2020-10-25 02:20:00 | +----+---------------------+--------------------+---------------------+ 9 rows in set (0.00 sec) -------------- select "id 4 does not match the WHERE clause!" -------------- +---------------------------------------+ | id 4 does not match the WHERE clause! | +---------------------------------------+ | id 4 does not match the WHERE clause! | +---------------------------------------+ 1 row in set (0.00 sec) -------------- select *, unix_timestamp(ts), cast(ts AT TIME ZONE '+00:00' AS DATETIME) as `utc_time` from ts where ts > '2020-10-25 02:20:00+01:00' -------------- +----+---------------------+--------------------+---------------------+ | id | ts | unix_timestamp(ts) | utc_time | +----+---------------------+--------------------+---------------------+ | 4 | 2020-10-25 02:40:00 | 1603586400 | 2020-10-25 00:40:00 | | 7 | 2020-10-25 02:40:00 | 1603590000 | 2020-10-25 01:40:00 | | 8 | 2020-10-25 03:00:00 | 1603591200 | 2020-10-25 02:00:00 | | 9 | 2020-10-25 03:20:00 | 1603592400 | 2020-10-25 02:20:00 | +----+---------------------+--------------------+---------------------+ 4 rows in set (0.00 sec) -------------- explain analyze select *, unix_timestamp(ts), cast(ts AT TIME ZONE '+00:00' AS DATETIME) as `utc_time` from ts where ts > '2020-10-25 02:20:00+01:00' -------------- +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Filter: (ts.ts > TIMESTAMP'2020-10-25 02:20:00') (cost=1.15 rows=3) (actual time=0.022..0.027 rows=4 loops=1) -> Table scan on ts (cost=1.15 rows=9) (actual time=0.017..0.022 rows=9 loops=1) | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) -------------- select "Where is id 5 & 6?" -------------- +--------------------+ | Where is id 5 & 6? | +--------------------+ | Where is id 5 & 6? | +--------------------+ 1 row in set (0.00 sec) -------------- select *, unix_timestamp(ts), cast(ts AT TIME ZONE '+00:00' AS DATETIME) as `utc_time` from ts where ts > '2020-10-25 02:20:00+02:00' -------------- +----+---------------------+--------------------+---------------------+ | id | ts | unix_timestamp(ts) | utc_time | +----+---------------------+--------------------+---------------------+ | 4 | 2020-10-25 02:40:00 | 1603586400 | 2020-10-25 00:40:00 | | 7 | 2020-10-25 02:40:00 | 1603590000 | 2020-10-25 01:40:00 | | 8 | 2020-10-25 03:00:00 | 1603591200 | 2020-10-25 02:00:00 | | 9 | 2020-10-25 03:20:00 | 1603592400 | 2020-10-25 02:20:00 | +----+---------------------+--------------------+---------------------+ 4 rows in set (0.00 sec) -------------- explain analyze select *, unix_timestamp(ts), cast(ts AT TIME ZONE '+00:00' AS DATETIME) as `utc_time` from ts where ts > '2020-10-25 02:20:00+02:00' -------------- +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Filter: (ts.ts > TIMESTAMP'2020-10-25 02:20:00') (cost=1.15 rows=3) (actual time=0.011..0.017 rows=4 loops=1) -> Table scan on ts (cost=1.15 rows=9) (actual time=0.009..0.014 rows=9 loops=1) | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) -------------- explain select *, unix_timestamp(ts), cast(ts AT TIME ZONE '+00:00' AS DATETIME) as `utc_time` from ts where ts > '2020-10-25 02:20:00+01:00' -------------- +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | ts | NULL | ALL | NULL | NULL | NULL | NULL | 9 | 33.33 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.01 sec) -------------- show warnings -------------- +-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Note | 1003 | /* select#1 */ select `mysql`.`ts`.`id` AS `id`,`mysql`.`ts`.`ts` AS `ts`,unix_timestamp(`mysql`.`ts`.`ts`) AS `unix_timestamp(ts)`,cast(time_zone(`mysql`.`ts`.`ts`) as datetime) AS `utc_time` from `mysql`.`ts` where (`mysql`.`ts`.`ts` > TIMESTAMP'2020-10-25 02:20:00') | +-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) -------------- select "In the warning above, the generated query has not adjusted for the timezone offset, and how could we know what epoch/time TIMESTAMP'2020-10-25 02:20:00' means?" -------------- +-----------------------------------------------------------------------------------------------------------------------------------------------------------------+ | In the warning above, the generated query has not adjusted for the timezone offset, and how could we know what epoch/time TIMESTAMP'2020-10-25 02:20:00' means? | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------+ | In the warning above, the generated query has not adjusted for the timezone offset, and how could we know what epoch/time TIMESTAMP'2020-10-25 02:20:00' means? | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) -------------- explain select *, unix_timestamp(ts), cast(ts AT TIME ZONE '+00:00' AS DATETIME) as `utc_time` from ts where ts > '2020-10-25 02:20:00+02:00' -------------- +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | ts | NULL | ALL | NULL | NULL | NULL | NULL | 9 | 33.33 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) -------------- show warnings -------------- +-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Note | 1003 | /* select#1 */ select `mysql`.`ts`.`id` AS `id`,`mysql`.`ts`.`ts` AS `ts`,unix_timestamp(`mysql`.`ts`.`ts`) AS `unix_timestamp(ts)`,cast(time_zone(`mysql`.`ts`.`ts`) as datetime) AS `utc_time` from `mysql`.`ts` where (`mysql`.`ts`.`ts` > TIMESTAMP'2020-10-25 02:20:00') | +-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) -------------- select "In the warning above, the generated query has not adjusted for the timezone offset, and how could we know what epoch/time TIMESTAMP'2020-10-25 02:20:00' means?" -------------- +-----------------------------------------------------------------------------------------------------------------------------------------------------------------+ | In the warning above, the generated query has not adjusted for the timezone offset, and how could we know what epoch/time TIMESTAMP'2020-10-25 02:20:00' means? | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------+ | In the warning above, the generated query has not adjusted for the timezone offset, and how could we know what epoch/time TIMESTAMP'2020-10-25 02:20:00' means? | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) -------------- set time_zone = '+00:00' -------------- Query OK, 0 rows affected (0.00 sec) -------------- select @@version, @@time_zone -------------- +-----------+-------------+ | @@version | @@time_zone | +-----------+-------------+ | 8.0.22 | +00:00 | +-----------+-------------+ 1 row in set (0.00 sec) -------------- alter table ts add index (ts) -------------- Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 -------------- show create table ts -------------- +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ts | CREATE TABLE `ts` ( `id` int unsigned NOT NULL AUTO_INCREMENT, `ts` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `ts` (`ts`) ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci | +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) -------------- select *, unix_timestamp(ts), cast(ts AT TIME ZONE '+00:00' AS DATETIME) as `utc_time` from ts -------------- +----+---------------------+--------------------+---------------------+ | id | ts | unix_timestamp(ts) | utc_time | +----+---------------------+--------------------+---------------------+ | 1 | 2020-10-24 23:40:00 | 1603582800 | 2020-10-24 23:40:00 | | 2 | 2020-10-25 00:00:00 | 1603584000 | 2020-10-25 00:00:00 | | 3 | 2020-10-25 00:20:00 | 1603585200 | 2020-10-25 00:20:00 | | 4 | 2020-10-25 00:40:00 | 1603586400 | 2020-10-25 00:40:00 | | 5 | 2020-10-25 01:00:00 | 1603587600 | 2020-10-25 01:00:00 | | 6 | 2020-10-25 01:20:00 | 1603588800 | 2020-10-25 01:20:00 | | 7 | 2020-10-25 01:40:00 | 1603590000 | 2020-10-25 01:40:00 | | 8 | 2020-10-25 02:00:00 | 1603591200 | 2020-10-25 02:00:00 | | 9 | 2020-10-25 02:20:00 | 1603592400 | 2020-10-25 02:20:00 | +----+---------------------+--------------------+---------------------+ 9 rows in set (0.00 sec) -------------- select *, unix_timestamp(ts), cast(ts AT TIME ZONE '+00:00' AS DATETIME) as `utc_time` from ts where ts > '2020-10-25 02:20:00+01:00' -------------- +----+---------------------+--------------------+---------------------+ | id | ts | unix_timestamp(ts) | utc_time | +----+---------------------+--------------------+---------------------+ | 7 | 2020-10-25 01:40:00 | 1603590000 | 2020-10-25 01:40:00 | | 8 | 2020-10-25 02:00:00 | 1603591200 | 2020-10-25 02:00:00 | | 9 | 2020-10-25 02:20:00 | 1603592400 | 2020-10-25 02:20:00 | +----+---------------------+--------------------+---------------------+ 3 rows in set (0.01 sec) -------------- select *, unix_timestamp(ts), cast(ts AT TIME ZONE '+00:00' AS DATETIME) as `utc_time` from ts where ts > '2020-10-25 02:20:00+02:00' -------------- +----+---------------------+--------------------+---------------------+ | id | ts | unix_timestamp(ts) | utc_time | +----+---------------------+--------------------+---------------------+ | 4 | 2020-10-25 00:40:00 | 1603586400 | 2020-10-25 00:40:00 | | 5 | 2020-10-25 01:00:00 | 1603587600 | 2020-10-25 01:00:00 | | 6 | 2020-10-25 01:20:00 | 1603588800 | 2020-10-25 01:20:00 | | 7 | 2020-10-25 01:40:00 | 1603590000 | 2020-10-25 01:40:00 | | 8 | 2020-10-25 02:00:00 | 1603591200 | 2020-10-25 02:00:00 | | 9 | 2020-10-25 02:20:00 | 1603592400 | 2020-10-25 02:20:00 | +----+---------------------+--------------------+---------------------+ 6 rows in set (0.00 sec) -------------- explain select *, unix_timestamp(ts), cast(ts AT TIME ZONE '+00:00' AS DATETIME) as `utc_time` from ts where ts > '2020-10-25 02:20:00+01:00' -------------- +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | ts | NULL | range | ts | ts | 5 | NULL | 3 | 100.00 | Using where; Using index | +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec) -------------- show warnings -------------- +-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Note | 1003 | /* select#1 */ select `mysql`.`ts`.`id` AS `id`,`mysql`.`ts`.`ts` AS `ts`,unix_timestamp(`mysql`.`ts`.`ts`) AS `unix_timestamp(ts)`,cast(time_zone(`mysql`.`ts`.`ts`) as datetime) AS `utc_time` from `mysql`.`ts` where (`mysql`.`ts`.`ts` > TIMESTAMP'2020-10-25 01:20:00') | +-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) -------------- explain select *, unix_timestamp(ts), cast(ts AT TIME ZONE '+00:00' AS DATETIME) as `utc_time` from ts where ts > '2020-10-25 02:20:00+02:00' -------------- +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | ts | NULL | range | ts | ts | 5 | NULL | 6 | 100.00 | Using where; Using index | +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec) -------------- show warnings -------------- +-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Note | 1003 | /* select#1 */ select `mysql`.`ts`.`id` AS `id`,`mysql`.`ts`.`ts` AS `ts`,unix_timestamp(`mysql`.`ts`.`ts`) AS `unix_timestamp(ts)`,cast(time_zone(`mysql`.`ts`.`ts`) as datetime) AS `utc_time` from `mysql`.`ts` where (`mysql`.`ts`.`ts` > TIMESTAMP'2020-10-25 00:20:00') | +-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) -------------- set time_zone = 'CET' -------------- Query OK, 0 rows affected (0.00 sec) -------------- select @@version, @@time_zone -------------- +-----------+-------------+ | @@version | @@time_zone | +-----------+-------------+ | 8.0.22 | CET | +-----------+-------------+ 1 row in set (0.00 sec) -------------- select *, unix_timestamp(ts), cast(ts AT TIME ZONE '+00:00' AS DATETIME) as `utc_time` from ts -------------- +----+---------------------+--------------------+---------------------+ | id | ts | unix_timestamp(ts) | utc_time | +----+---------------------+--------------------+---------------------+ | 1 | 2020-10-25 01:40:00 | 1603582800 | 2020-10-24 23:40:00 | | 2 | 2020-10-25 02:00:00 | 1603584000 | 2020-10-25 00:00:00 | | 3 | 2020-10-25 02:20:00 | 1603585200 | 2020-10-25 00:20:00 | | 4 | 2020-10-25 02:40:00 | 1603586400 | 2020-10-25 00:40:00 | | 5 | 2020-10-25 02:00:00 | 1603587600 | 2020-10-25 01:00:00 | | 6 | 2020-10-25 02:20:00 | 1603588800 | 2020-10-25 01:20:00 | | 7 | 2020-10-25 02:40:00 | 1603590000 | 2020-10-25 01:40:00 | | 8 | 2020-10-25 03:00:00 | 1603591200 | 2020-10-25 02:00:00 | | 9 | 2020-10-25 03:20:00 | 1603592400 | 2020-10-25 02:20:00 | +----+---------------------+--------------------+---------------------+ 9 rows in set (0.00 sec) -------------- select "id 4 does not match the WHERE clause!" -------------- +---------------------------------------+ | id 4 does not match the WHERE clause! | +---------------------------------------+ | id 4 does not match the WHERE clause! | +---------------------------------------+ 1 row in set (0.00 sec) -------------- select *, unix_timestamp(ts), cast(ts AT TIME ZONE '+00:00' AS DATETIME) as `utc_time` from ts where ts > '2020-10-25 02:20:00+01:00' -------------- +----+---------------------+--------------------+---------------------+ | id | ts | unix_timestamp(ts) | utc_time | +----+---------------------+--------------------+---------------------+ | 4 | 2020-10-25 02:40:00 | 1603586400 | 2020-10-25 00:40:00 | | 7 | 2020-10-25 02:40:00 | 1603590000 | 2020-10-25 01:40:00 | | 8 | 2020-10-25 03:00:00 | 1603591200 | 2020-10-25 02:00:00 | | 9 | 2020-10-25 03:20:00 | 1603592400 | 2020-10-25 02:20:00 | +----+---------------------+--------------------+---------------------+ 4 rows in set (0.00 sec) -------------- explain analyze select *, unix_timestamp(ts), cast(ts AT TIME ZONE '+00:00' AS DATETIME) as `utc_time` from ts where ts > '2020-10-25 02:20:00+01:00' -------------- +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Filter: (ts.ts > TIMESTAMP'2020-10-25 02:20:00') (cost=1.46 rows=6) (actual time=0.010..0.014 rows=4 loops=1) -> Index range scan on ts using ts (cost=1.46 rows=6) (actual time=0.009..0.011 rows=6 loops=1) | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) -------------- select "Where is id 5 & 6?" -------------- +--------------------+ | Where is id 5 & 6? | +--------------------+ | Where is id 5 & 6? | +--------------------+ 1 row in set (0.00 sec) -------------- select *, unix_timestamp(ts), cast(ts AT TIME ZONE '+00:00' AS DATETIME) as `utc_time` from ts where ts > '2020-10-25 02:20:00+02:00' -------------- +----+---------------------+--------------------+---------------------+ | id | ts | unix_timestamp(ts) | utc_time | +----+---------------------+--------------------+---------------------+ | 4 | 2020-10-25 02:40:00 | 1603586400 | 2020-10-25 00:40:00 | | 7 | 2020-10-25 02:40:00 | 1603590000 | 2020-10-25 01:40:00 | | 8 | 2020-10-25 03:00:00 | 1603591200 | 2020-10-25 02:00:00 | | 9 | 2020-10-25 03:20:00 | 1603592400 | 2020-10-25 02:20:00 | +----+---------------------+--------------------+---------------------+ 4 rows in set (0.00 sec) -------------- explain analyze select *, unix_timestamp(ts), cast(ts AT TIME ZONE '+00:00' AS DATETIME) as `utc_time` from ts where ts > '2020-10-25 02:20:00+02:00' -------------- +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Filter: (ts.ts > TIMESTAMP'2020-10-25 02:20:00') (cost=1.46 rows=6) (actual time=0.016..0.020 rows=4 loops=1) -> Index range scan on ts using ts (cost=1.46 rows=6) (actual time=0.013..0.017 rows=6 loops=1) | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) -------------- explain select *, unix_timestamp(ts), cast(ts AT TIME ZONE '+00:00' AS DATETIME) as `utc_time` from ts where ts > '2020-10-25 02:20:00+01:00' -------------- +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | ts | NULL | range | ts | ts | 5 | NULL | 6 | 100.00 | Using where; Using index | +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec) -------------- show warnings -------------- +-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Note | 1003 | /* select#1 */ select `mysql`.`ts`.`id` AS `id`,`mysql`.`ts`.`ts` AS `ts`,unix_timestamp(`mysql`.`ts`.`ts`) AS `unix_timestamp(ts)`,cast(time_zone(`mysql`.`ts`.`ts`) as datetime) AS `utc_time` from `mysql`.`ts` where (`mysql`.`ts`.`ts` > TIMESTAMP'2020-10-25 02:20:00') | +-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) -------------- select "In the warning above, the generated query has not adjusted for the timezone offset, and how could we know what epoch/time TIMESTAMP'2020-10-25 02:20:00' means?" -------------- +-----------------------------------------------------------------------------------------------------------------------------------------------------------------+ | In the warning above, the generated query has not adjusted for the timezone offset, and how could we know what epoch/time TIMESTAMP'2020-10-25 02:20:00' means? | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------+ | In the warning above, the generated query has not adjusted for the timezone offset, and how could we know what epoch/time TIMESTAMP'2020-10-25 02:20:00' means? | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) -------------- explain analyze select *, unix_timestamp(ts), cast(ts AT TIME ZONE '+00:00' AS DATETIME) as `utc_time` from ts where ts > '2020-10-25 02:20:00+01:00' -------------- +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Filter: (ts.ts > TIMESTAMP'2020-10-25 02:20:00') (cost=1.46 rows=6) (actual time=0.036..0.044 rows=4 loops=1) -> Index range scan on ts using ts (cost=1.46 rows=6) (actual time=0.033..0.040 rows=6 loops=1) | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) -------------- explain select *, unix_timestamp(ts), cast(ts AT TIME ZONE '+00:00' AS DATETIME) as `utc_time` from ts where ts > '2020-10-25 02:20:00+02:00' -------------- +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | ts | NULL | range | ts | ts | 5 | NULL | 6 | 100.00 | Using where; Using index | +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec) -------------- show warnings -------------- +-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Note | 1003 | /* select#1 */ select `mysql`.`ts`.`id` AS `id`,`mysql`.`ts`.`ts` AS `ts`,unix_timestamp(`mysql`.`ts`.`ts`) AS `unix_timestamp(ts)`,cast(time_zone(`mysql`.`ts`.`ts`) as datetime) AS `utc_time` from `mysql`.`ts` where (`mysql`.`ts`.`ts` > TIMESTAMP'2020-10-25 02:20:00') | +-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) -------------- explain analyze select *, unix_timestamp(ts), cast(ts AT TIME ZONE '+00:00' AS DATETIME) as `utc_time` from ts where ts > '2020-10-25 02:20:00+02:00' -------------- +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Filter: (ts.ts > TIMESTAMP'2020-10-25 02:20:00') (cost=1.46 rows=6) (actual time=0.015..0.019 rows=4 loops=1) -> Index range scan on ts using ts (cost=1.46 rows=6) (actual time=0.013..0.017 rows=6 loops=1) | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) -------------- select "In the warning above, the generated query has not adjusted for the timezone offset, and how could we know what epoch/time TIMESTAMP'2020-10-25 02:20:00' means?" -------------- +-----------------------------------------------------------------------------------------------------------------------------------------------------------------+ | In the warning above, the generated query has not adjusted for the timezone offset, and how could we know what epoch/time TIMESTAMP'2020-10-25 02:20:00' means? | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------+ | In the warning above, the generated query has not adjusted for the timezone offset, and how could we know what epoch/time TIMESTAMP'2020-10-25 02:20:00' means? | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) -------------- drop table ts -------------- Query OK, 0 rows affected (0.01 sec) -------------- set time_zone = 'CET' -------------- Query OK, 0 rows affected (0.00 sec)