Description:
column_default value on information_schema.column doesn't include any timezone information.
How to repeat:
mysql80 16> SELECT @@time_zone;
+-------------+
| @@time_zone |
+-------------+
| +00:00 |
+-------------+
1 row in set (0.00 sec)
mysql80 16> CREATE TABLE t_0000 (ts timestamp DEFAULT '2000-01-01 00:00:00');
Query OK, 0 rows affected (0.02 sec)
mysql80 16> SET SESSION time_zone = '+09:00';
Query OK, 0 rows affected (0.00 sec)
mysql80 16> SELECT @@time_zone;
+-------------+
| @@time_zone |
+-------------+
| +09:00 |
+-------------+
1 row in set (0.00 sec)
mysql80 16> CREATE TABLE t_0900 (ts timestamp DEFAULT '2000-01-01 00:00:00'); -- this is diferrent time than 2000-01-01 00:00:00+00:00
Query OK, 0 rows affected (0.02 sec)
### Can distinguish them SHOW CREATE TABLE
mysql80 16> SHOW CREATE TABLE t_0000\G
*************************** 1. row ***************************
Table: t_0000
Create Table: CREATE TABLE `t_0000` (
`ts` timestamp NULL DEFAULT '2000-01-01 00:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
mysql80 16> SHOW CREATE TABLE t_0900\G
*************************** 1. row ***************************
Table: t_0900
Create Table: CREATE TABLE `t_0900` (
`ts` timestamp NULL DEFAULT '1999-12-31 15:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
### But can't distinguish information_schema.columns.column_default
mysql80 16> SELECT * FROM information_schema.columns WHERE table_schema = 'd1';
+---------------+--------------+------------+-------------+------------------+---------------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+----------------+-------------+------------+-------+---------------------------------+----------------+-----------------------+--------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | ORDINAL_POSITION | COLUMN_DEFAULT | IS_NULLABLE | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH | NUMERIC_PRECISION | NUMERIC_SCALE | DATETIME_PRECISION | CHARACTER_SET_NAME | COLLATION_NAME | COLUMN_TYPE | COLUMN_KEY | EXTRA | PRIVILEGES | COLUMN_COMMENT | GENERATION_EXPRESSION | SRS_ID |
+---------------+--------------+------------+-------------+------------------+---------------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+----------------+-------------+------------+-------+---------------------------------+----------------+-----------------------+--------+
| def | d1 | t_0000 | ts | 1 | 2000-01-01 00:00:00 | YES | timestamp | NULL | NULL | NULL | NULL | 0 | NULL | NULL | timestamp | | | select,insert,update,references | | | NULL |
| def | d1 | t_0900 | ts | 1 | 2000-01-01 00:00:00 | YES | timestamp | NULL | NULL | NULL | NULL | 0 | NULL | NULL | timestamp | | | select,insert,update,references | | | NULL |
+---------------+--------------+------------+-------------+------------------+---------------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+----------------+-------------+------------+-------+---------------------------------+----------------+-----------------------+--------+
2 rows in set (0.00 sec)
Suggested fix:
Add timezone info (ex. 2000-01-01 00:00:00+00:00) or convert to display as same as SHOW CREATE TABLE on informaiton_schema.columns.
Description: column_default value on information_schema.column doesn't include any timezone information. How to repeat: mysql80 16> SELECT @@time_zone; +-------------+ | @@time_zone | +-------------+ | +00:00 | +-------------+ 1 row in set (0.00 sec) mysql80 16> CREATE TABLE t_0000 (ts timestamp DEFAULT '2000-01-01 00:00:00'); Query OK, 0 rows affected (0.02 sec) mysql80 16> SET SESSION time_zone = '+09:00'; Query OK, 0 rows affected (0.00 sec) mysql80 16> SELECT @@time_zone; +-------------+ | @@time_zone | +-------------+ | +09:00 | +-------------+ 1 row in set (0.00 sec) mysql80 16> CREATE TABLE t_0900 (ts timestamp DEFAULT '2000-01-01 00:00:00'); -- this is diferrent time than 2000-01-01 00:00:00+00:00 Query OK, 0 rows affected (0.02 sec) ### Can distinguish them SHOW CREATE TABLE mysql80 16> SHOW CREATE TABLE t_0000\G *************************** 1. row *************************** Table: t_0000 Create Table: CREATE TABLE `t_0000` ( `ts` timestamp NULL DEFAULT '2000-01-01 00:00:00' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec) mysql80 16> SHOW CREATE TABLE t_0900\G *************************** 1. row *************************** Table: t_0900 Create Table: CREATE TABLE `t_0900` ( `ts` timestamp NULL DEFAULT '1999-12-31 15:00:00' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec) ### But can't distinguish information_schema.columns.column_default mysql80 16> SELECT * FROM information_schema.columns WHERE table_schema = 'd1'; +---------------+--------------+------------+-------------+------------------+---------------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+----------------+-------------+------------+-------+---------------------------------+----------------+-----------------------+--------+ | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | ORDINAL_POSITION | COLUMN_DEFAULT | IS_NULLABLE | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH | NUMERIC_PRECISION | NUMERIC_SCALE | DATETIME_PRECISION | CHARACTER_SET_NAME | COLLATION_NAME | COLUMN_TYPE | COLUMN_KEY | EXTRA | PRIVILEGES | COLUMN_COMMENT | GENERATION_EXPRESSION | SRS_ID | +---------------+--------------+------------+-------------+------------------+---------------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+----------------+-------------+------------+-------+---------------------------------+----------------+-----------------------+--------+ | def | d1 | t_0000 | ts | 1 | 2000-01-01 00:00:00 | YES | timestamp | NULL | NULL | NULL | NULL | 0 | NULL | NULL | timestamp | | | select,insert,update,references | | | NULL | | def | d1 | t_0900 | ts | 1 | 2000-01-01 00:00:00 | YES | timestamp | NULL | NULL | NULL | NULL | 0 | NULL | NULL | timestamp | | | select,insert,update,references | | | NULL | +---------------+--------------+------------+-------------+------------------+---------------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+----------------+-------------+------------+-------+---------------------------------+----------------+-----------------------+--------+ 2 rows in set (0.00 sec) Suggested fix: Add timezone info (ex. 2000-01-01 00:00:00+00:00) or convert to display as same as SHOW CREATE TABLE on informaiton_schema.columns.