What are Temporal datatypes
Temporal Datatypes are basically the Date Time data types of MySQL like TIME, DATETIME and TIMESTAMP. They are used for different purposes in MySQL database tables for audit record of the rows inserted into the tables and storing the Date values along with the records.
What is the different between Old and New
Temporal types are updated in newer version of MySQL 5.6 and onward. There are fractional second precision added in seconds of temporal types with up to microseconds along with fractional second precision. Storage of old temporal types are not space efficient. Old temporal types of MySQL Version 5.5 are shown below.
Old temporal types are created in MySQL 5.5 and older versions. Any new values created with temporal types after MySQL 5.6.4 will automatically get the new version of it and fractional second precision. As MySQL is now version 8 is going on so support for old version is not there. Now updating from older version of MySQL to latest version is not possible with old temporal types.
How to Upgrade
Now question is how to upgrade the old types to newer one when switching data from older version to the newer version. ALTER, REPAIR TABLE command. For some cases mysql_upgrade will do the job and upgrade the data to version 5.7. There is one more way to avoid the temporal types by using the -avoid-temporal-upgrade in the configuration file.
Alter Command
Tools and Techniques
But this ALTER command is suitable for small tables where table size is already small for big tables it will take much more time as compaction of table is a long time taking process. To avoid blocking of table for a long period of time. Tools like gh-ost can be used for this job.
Or we can create one more similar table and copy the data in that table.
Summary
This change is not necessary for people who are upgrading their system by restoring the data done by mysqldump. By migrating the data from old temporal types to new one we can save space and get more accuracy.