Does MySQL store date types in int, timestamp, or datetime?

usually storage time with datetime type, and now many systems also use int storage time, what difference do they have? Individuals prefer to use int this is good for date calculations, so let's look at the kind of things that would be better. int(1). 4 bytes of storage, the length of int is 4 bytes, storage space is less than datatime, int index storage space is relatively small, sorting and query efficiency is relatively high a little (2very poor readability, unable to visually see the data, may make you very annoyed timestamp (1) 4 bytes of storage (2) value is saved in UTC format (3time zone conversion, which is converted to the current time zone when it is stored, and then converted back to the current time zone. (4) The timestamp value cannot be earlier than 1970 or later than 2037datetime (1) 8 bytes of storage (2) is independent of the time zone (3) to'YYYY-MM-DD HH:MM:SS'Format to retrieve and display datetime values. The scope of support is'1000-01-01 00:00:00'To'9999-12-31 23:59:59'MySQL is also in these two years of popularity, performance more and more, how to store to see personal habits and project needs to share two articles about the int vs timestamp vs datetime performance Test Myisam:mysql datetime vs Timestamp VS INT Tester Create TABLE ' test_datetime ' (' ID 'int(Ten) unsigned not NULL auto_increment, ' datetime ' FieldType not null,primary KEY (' id ')) ENGINE=MyISAM; model configuration Kip-Lockingkey_buffer=128mmax_allowed_packet=1mtable_cache= +sort_buffer_size=2mread_buffer_size=2mread_rnd_buffer_size=8mmyisam_sort_buffer_size=8mthread_cache_size=8Query_cache_type=0query_cache_size=0thread_concurrency=4Test datetime14111 14010        14369     130000000TIMESTAMP13888        13887        14122     90000000INT13270        12970        13496     90000000Executive Mysqlmysql>Select* fromTest_datetime into outfile '/tmp/Test_datetime.sql '; Query OK,10000000Rows Affected (6.19sec) MySQL>Select* fromTest_timestamp into outfile '/tmp/Test_timestamp.sql '; Query OK,10000000Rows Affected (8.75sec) MySQL>Select* fromTest_int into outfile '/tmp/Test_int.sql '; Query OK,10000000Rows Affected (4.29sec) ALTER TABLE test_datetime rename test_int;alter table test_int add column datetimeint int not null;update test_in TSetDatetimeint =Unix_timestamp (datetime); ALTER TABLE test_int drop column datetime;alter table test_int change column Datetimeint dat ETimeintNotNULL;Select* fromTest_int into outfile '/tmp/test_int2.sql ';d rop table test_int; So now I have exactly the same timestamps fromThe DATETIME test, and it'll be is possible to reuse the originals forTIMESTAMP Tests asWell.mysql> Load Data infile '/export/home/ntavares/test_datetime.sql ' into table test_datetime; Query OK,10000000Rows Affected (41.52sec) Records:10000000Deleted:0Skipped:0Warnings:0MySQL> Load Data infile '/export/home/ntavares/test_datetime.sql ' into table test_timestamp; Query OK,10000000Rows affected, -Warnings (48.32sec) Records:10000000Deleted:0Skipped:0Warnings: -MySQL> Load Data infile '/export/home/ntavares/test_int2.sql ' into table test_int; Query OK,10000000Rows Affected (37.73sec) Records:10000000Deleted:0Skipped:0Warnings:0as expected, since INT isSimply stored as  is  whileThe others has to be recalculated. Notice How TIMESTAMP still performs worse, even though uses half of DATETIME storage size. Let's check the performance of full table Scan:mysql> SELECT sql_no_cache count (id) from Test_datetime WHERE datetime > '1970- on- on  on: -:xx′and datetime < '1970- on- on  on: *:xx;+ ——— –+| Count (ID) |+ ——— –+|211991|+ ——— –+1Rowinch Set(3.93sec) MySQL> SELECT sql_no_cache count (id) from Test_timestamp WHERE datetime > '1970- on- on  on: -:xx′and datetime < '1970- on- on  on: *:xx;+ ——— –+| Count (ID) |+ ——— –+|211991|+ ——— –+1Rowinch Set(9.87sec) MySQL> SELECT sql_no_cache count (id) from Test_int WHERE datetime > Unix_timestamp ('1970- on- on  on: -:xx) and DateTime < Unix_timestamp ('1970- on- on  on: *:xx);+ ——— –+| Count (ID) |+ ——— –+|211991|+ ——— –+1Rowinch Set(15.12sec) Then again, TIMESTAMP performs worse and the recalculations seemed to impact, so the next good thing to test seemed to be Without those recalculations:find the equivalents of those unix_timestamp () values, and use them Instead:mysql>SelectUnix_timestamp ('1970- on- on  on: -:xxAs lower, Unix_timestamp ('1970- on- on  on: *:xx) as bigger;+ ——-+--–+| Lower | Bigger |+ ——-+--–+|1800|2100|+ ——-+--–+1Rowinch Set(0.00sec) MySQL> SELECT sql_no_cache count (id) from Test_int WHERE datetime >1800and DateTime <2100;+ ——— –+| Count (ID) |+ ——— –+|211991|+ ——— –+1Rowinch Set(1.94Sec

Does MySQL store date types in int, timestamp, or datetime?