is the MySQL store date type int, timestamp, or datetime?

Int

(1). 4 byte storage, the length of the int is 4 bytes, the storage space is less than datatime, the int index storage space is also relatively small, the sorting and the query efficiency is relatively higher a little
(2) The readability is extremely poor, cannot see the data intuitively, may make you very irritated

TIMESTAMP

(1) 4 bytes Storage
(2) Value saved in UTC format
(3) Time zone conversion, storage time for the current time zone conversion, retrieval and then converted back to the current time zone.
(4) Timestamp value cannot be earlier than 1970 or later than 2037

Datetime

(1) 8 bytes Storage
(2) Independent of time zone

(3) Retrieve and display datetime values in ' yyyy-mm-dd HH:MM:SS ' format. The range of support is ' 1000-01-01 00:00:00 ' to ' 9999-12-31 23:59:59 '

MySQL is also in the past two years, the performance of more and more, specific how to store to see personal habits and project needs bar
Share two articles on the int vs timestamp vs datetime performance Test

Myisam:mysql DATETIME vs TIMESTAMP vs INT tester

CREATE TABLE ' Test_datetime ' (
' ID ' int (a) unsigned not NULL auto_increment,
' DateTime ' FieldType not NULL,
PRIMARY KEY (' id ')
) Engine=myisam;

Model configuration

Kip-locking
Key_buffer = 128M
Max_allowed_packet = 1M
Table_cache = 512
Sort_buffer_size = 2M
Read_buffer_size = 2M
Read_rnd_buffer_size = 8M
Myisam_sort_buffer_size = 8M
Thread_cache_size = 8
Query_cache_type = 0
Query_cache_size = 0
Thread_concurrency = 4

Test

DATETIME 14111 14010 14369 130000000
TIMESTAMP 13888 13887 14122 90000000
INT 13270 12970 13496 90000000

Execute MySQL

Mysql> SELECT * from Test_datetime into outfile '/tmp/test_datetime.sql ';
Query OK, 10000000 rows Affected (6.19 sec)

Mysql> SELECT * from Test_timestamp into outfile '/tmp/test_timestamp.sql ';
Query OK, 10000000 rows affected (8.75 sec)

Mysql> SELECT * from Test_int into outfile '/tmp/test_int.sql ';
Query OK, 10000000 rows Affected (4.29 sec)

ALTER TABLE test_datetime rename Test_int;
ALTER TABLE test_int add column datetimeint int not NULL;
Update Test_int Set datetimeint = Unix_timestamp (datetime);
ALTER TABLE test_int drop column datetime;
ALTER TABLE test_int change column datetimeint datetime int is not NULL;
SELECT * from Test_int into outfile '/tmp/test_int2.sql ';
drop table Test_int;

So-I have exactly the same timestamps from the DATETIME test, and it'll be possible to reuse the Tamp tests as.

mysql> load Data infile '/export/home/ntavares/test_datetime.sql ' into table test_datetime;
Query OK, 10000000 rows Affected (41.52 sec)
records:10000000 deleted:0 skipped:0 warnings:0

mysql> load Data infile '/export/home/ntavares/test_datetime.sql ' into table test_timestamp;
Query OK, 10000000 rows affected, Warnings (48.32 sec)
records:10000000 deleted:0 skipped:0 warnings:44

mysql> load Data infile '/export/home/ntavares/test_int2.sql ' into table test_int;
Query OK, 10000000 rows Affected (37.73 sec)
records:10000000 deleted:0 skipped:0 warnings:0

As expected, since INT is simply stored as was while the others have to be recalculated. Notice How TIMESTAMP still performs worse, even though uses half of DATETIME storage size.

Let ' s check the performance's full table scan:

Mysql> SELECT Sql_no_cache count (id) from Test_datetime WHERE datetime > ' 1970-01-01 01:30:00′and datetime < ' 19 70-01-01 01:35:00′;
+ ——— –+
| Count (ID) |
+ ——— –+
| 211991 |
+ ——— –+
1 row in Set (3.93 sec)

Mysql> SELECT Sql_no_cache count (id) from Test_timestamp WHERE datetime > ' 1970-01-01 01:30:00′and datetime < ' 1 970-01-01 01:35:00′;
+ ——— –+
| Count (ID) |
+ ——— –+
| 211991 |
+ ——— –+
1 row in Set (9.87 sec)

Mysql> SELECT Sql_no_cache count (id) from Test_int WHERE datetime > Unix_timestamp (' 1970-01-01 01:30:00′) and Dateti Me < Unix_timestamp (' 1970-01-01 01:35:00′);
+ ——— –+
| Count (ID) |
+ ——— –+
| 211991 |
+ ——— –+
1 row in Set (15.12 sec)

Then again, TIMESTAMP performs worse and the recalculations seemed to impact, so the next good thing to test seemed to is Without those recalculations:find the equivalents of those unix_timestamp () values, and use them instead:

Mysql> Select Unix_timestamp (' 1970-01-01 01:30:00′) as lower, Unix_timestamp (' 1970-01-01 01:35:00′ ') as bigger;
+ ——-+--–+
| Lower | Bigger |
+ ——-+--–+
|   1800 | 2100 |
+ ——-+--–+
1 row in Set (0.00 sec)

Mysql> SELECT Sql_no_cache count (id) from Test_int WHERE datetime > 1800 and datetime < 2100;
+ ——— –+
| Count (ID) |
+ ——— –+
| 211991 |
+ ——— –+
1 row in Set (1.94 sec)