Time storage of mysql advanced part
Reproduced in guide: https://javagude.cn/database/mysql/some-thoughts-on-database-storage-time/
timestamp is better than datatime
1.datatime will cause time zone problems
2.datatime8 bytes timestamo4 bytes
1. Remember not to store dates in strings
I remember doing this when I was in college, and now many novices who don't know much about the database will do the same. It can be seen that this way of storing dates still has some advantages, that is, it is simple, straightforward and easy to start.
However, this is incorrect. There are two main problems:
- String takes up more space!
- The date stored in the string is inefficient (compare character by character), and can not be calculated and compared with the date related API.
# 2. Choice between datetime and Timestamp
Datetime and Timestamp are two similar data types of save time provided by MySQL. How should they choose between the two?
Usually we prefer Timestamp. Let's talk about why!
# 2.1 DateTime type has no time zone information
DateTime type has no time zone information (regardless of the time zone), the time saved by DateTime type is the time corresponding to the time zone set by the current session. What's the problem? When your time zone is changed, such as changing the address of your server or changing the time zone setting of the client connection, you will read the wrong time from the database. Don't underestimate this problem, which is common in many systems A lot of jokes have been made because of this problem.
Timestamp is related to time zone. The value of the timestamp field will change with the time zone of the server and will be automatically converted to the corresponding time. In other words, the value of this field will be different when the same record is queried in different time zones.
Let's actually demonstrate it!
Create table SQL statement:
CREATE TABLE `time_zone_test` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `date_time` datetime DEFAULT NULL, `time_stamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1
2
3
4
5
6
Insert data:
INSERT INTO time_zone_test(date_time,time_stamp) VALUES(NOW(),NOW());
View data:
select date_time,time_stamp from time_zone_test;
result:
+---------------------+---------------------+ | date_time | time_stamp | +---------------------+---------------------+ | 2020-01-11 09:53:32 | 2020-01-11 09:53:32 | +---------------------+---------------------+
Now I run
Modify the time zone of the current session:
set time_zone='+8:00';
View data:
+---------------------+---------------------+ | date_time | time_stamp | +---------------------+---------------------+ | 2020-01-11 09:53:32 | 2020-01-11 17:53:32 | +---------------------+---------------------+
Extension: some common sql commands about MySQL time zone setting
# View current session time zone SELECT @@session.time_zone; # Set the current session time zone SET time_zone = 'Europe/Helsinki'; SET time_zone = "+00:00"; # Database global time zone settings SELECT @@global.time_zone; # Set global time zone SET GLOBAL time_zone = '+8:00'; SET GLOBAL time_zone = 'Europe/Helsinki';
# 2.2 DateTime type consumes more space
Timestamp only needs 4 bytes of storage space, but DateTime needs 8 bytes of storage space. However, this also creates a problem. Timestamp represents a smaller time range.
- DateTime : 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
- Timestamp: 1970-01-01 00:00:01 ~ 2037-12-31 23:59:59
Timestamp is slightly different in different versions of MySQL.
# 3. Look at the MySQL date type storage space
The following figure shows the storage space occupied by date type in MySQL version 5.6:
It can be seen that 5.6 MySQL after 4 has an extra decimal place that needs 0 ~ 3 bytes. DateTime and Timestamp have different storage space occupation.
For convenience, we still default that Timestamp only needs 4 bytes of storage space, but DateTime needs 8 bytes of storage space.
# 4. Is numerical timestamp a better choice?
Many times, we also use int or bigint type values, that is, time stamps, to represent time.
This storage method has some advantages of Timestamp type, and its efficiency for date sorting and comparison will be higher. It is also very convenient across systems. After all, it is only stored values. The disadvantage is also obvious, that is, the readability of the data is too poor, and you can't intuitively see the specific time.
Timestamp is defined as follows:
The definition of time stamp is calculated from a reference time, which is "1970-1-1 00:00:00 + 0:00". From this time, it is expressed as an integer and measured in seconds. With the passage of time, the integer of time continues to increase. In this way, I only need a value to perfectly represent time, and this value is an absolute value, that is, the timestamp representing time is the same, the generated values are the same, and there is no time zone concept, so in the transmission of time in the system, There is no need for additional conversion. Only when it is displayed to the user can it be converted to the local time in string format.
Actual operation in database:
mysql> select UNIX_TIMESTAMP('2020-01-11 09:53:32');
+---------------------------------------+
| UNIX_TIMESTAMP('2020-01-11 09:53:32') |
+---------------------------------------+
| 1578707612 |
+---------------------------------------+
1 row in set (0.00 sec)
mysql> select FROM_UNIXTIME(1578707612);
+---------------------------+
| FROM_UNIXTIME(1578707612) |
+---------------------------+
| 2020-01-11 09:53:32 |
+---------------------------+
1 row in set (0.01 sec)
# 5. Summary
How to store time in MySQL? Datetime?Timestamp? Timestamp of value saving?
It seems that there is no silver bullet. Many programmers will think that the numerical Timestamp is really good, efficient and compatible, but many people think it is not intuitive enough. Here's a tip. The author of high performance MySQL recommends Timestamp because the numerical representation of time is not intuitive enough. The following is the original text:
Each method has its own advantages, and it is the king according to the actual scene. The following is a simple comparison of these three methods for you to choose the correct data type of storage time in actual development:
People think it is not intuitive enough. Here's a tip. The author of high performance MySQL recommends Timestamp because the numerical representation of time is not intuitive enough. The following is the original text:
[external chain picture transferring... (img-cbqu5f8-1640668702452)]
Each method has its own advantages, and it is the king according to the actual scene. The following is a simple comparison of these three methods for you to choose the correct data type of storage time in actual development:
