Show internal type for TIMESTAMP, DATETIME, and TIME columns

MySQL 5.6 and later supports the show_old_temporals system variable that configures the server to show internal type for TIMESTAMP, DATETIME, and TIME columns:

show_old_temporals

Property Value
Command-Line Format --show-old-temporals[=

Unknown macro: {OFF|ON}

]
Introduced 5.6.24
Deprecated 5.6.24
System Variable show_old_temporals
Scope Global, Session
Dynamic Yes
Type Boolean
Default Value OFF

Whether SHOW CREATE TABLE output includes comments to flag temporal columns found to be in pre-5.6.4 format (TIME, DATETIME, and TIMESTAMP columns without support for fractional seconds precision). This variable is disabled by default. If enabled, SHOW CREATE TABLE output looks like this:

CREATE TABLE `mytbl` (
`ts` timestamp /* 5.5 binary format */ NOT NULL DEFAULT CURRENT_TIMESTAMP,
`dt` datetime /* 5.5 binary format */ DEFAULT NULL,
`t` time /* 5.5 binary format */ DEFAULT NULL
) DEFAULT CHARSET=latin1
Output for the COLUMN_TYPE column of the INFORMATION_SCHEMA.COLUMNS table is affected similarly.

This variable was added in MySQL 5.6.24. It is deprecated and will be removed in a future MySQL release.

https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_show_old_temporals

Mixing old temporal types and new temporal types can cause a lot of issues with replication and with importing InnoDB tablespaces. It would be useful to have some easy way tell if a temporal column uses the MariaDB 5.3 format or the MySQL 5.6 format or the pre-MySQL 5.6 format. We should probably port this feature from MySQL, or re-implement a similar feature.