Explanation of the problem of selecting MySQL storage time type

The datetime type is usually used to store time in MySQL, but many systems now also use int to store unix timestamps. What is the difference between them? My summary is as follows:

int

(1) 4 bytes of storage. The length of INT is 4 bytes, which takes up less storage space than datatime. The storage space of int index is also relatively small, and the sorting and query efficiency is relatively high.

(2) The readability is extremely poor and the data cannot be seen intuitively

TIMESTAMP

(1) 4 bytes storage

(2) Values ​​are saved in UTC format

(3) Time zone conversion: convert to the current time zone when storing and convert back to the current time zone when retrieving.

(4) TIMESTAMP values ​​cannot be earlier than 1970 or later than 2037

datetime

(1) 8 bytes of storage

(2) Not related to time zone

(3) Retrieve and display DATETIME values ​​in 'YYYY-MM-DD HH:MM:SS' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'

As MySQL performance gets higher and higher, I think the storage method of time depends on personal habits and project requirements.

Share two articles about int vs timestamp vs datetime performance testing

MySQL DATETIME vs TIMESTAMP vs INT tester

CREATE TABLE `test_datetime` (
`id` int(10) 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 not null;
select * from test_int into outfile '/tmp/test_int2.sql';
drop table test_int;

So now I have exactly the same timestamps from the DATETIME test, and it will be possible to reuse the originals for TIMESTAMP tests as well.

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, 44 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 is 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 of full table scan:

mysql> SELECT SQL_NO_CACHE count(id) FROM test_datetime WHERE datetime > '1970-01-01 01:30:00′ AND datetime < '1970-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 < '1970-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 datetime < 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 be 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)

MySQL DATETIME vs TIMESTAMP vs INT performance and benchmarking with InnoDB

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM. If you want to learn more about this, please check out the following links