Best practices for MySQL storage time

Usually we need to record time in development , For example, it is used to record the creation time and modification time of a record . There are many ways to store time in a database , such as MySQL It provides the date type itself , such as DATETIME,TIMESTAMEP etc. , We can also store the timestamp directly as INT type , Others store time directly as a string type .

So which is the better way to store time ？

Don't use strings to store time types

It's a very easy mistake for beginners , It's easy to set the field directly to VARCHAR type , Storage "2021-01-01 00:00:00" Such a string . Of course, the advantage of this is that it's relatively simple , Quick start .

But it's strongly not recommended , Because there are two big problems in doing so ：

• Strings take up a lot of space
• The efficiency of the stored fields is too low , You can only compare character by character , Can't use MySQL Date provided API

MySQL The date type in

MySQL Common date types in databases are YEAR、DATE、TIME、DATETIME、TIMESTAMEP. Because you usually need to be precise to seconds , The more suitable ones are DATETIME,TIMESTAMEP.

DATETIME

DATETIME Stored in the database in the form of ：YYYY-MM-DD HH:MM:SS, Fixed occupancy 8 Bytes .

from MySQL 5.6 Version start ,DATETIME Type supports milliseconds ,DATETIME(N) Medium N Represents the precision of milliseconds . for example ,DATETIME(6) Indicates that you can store 6 The millisecond value of the bit .

TIMESTAMEP

TIMESTAMP The actual stored content is ‘1970-01-01 00:00:00’ The number of milliseconds up to now . stay MySQL in , Due to type TIMESTAMP Occupy 4 Bytes , Therefore, the storage time limit can only be up to ‘2038-01-19 03:14:07’.

from MySQL 5.6 Version start , type TIMESTAMP It can also support milliseconds . And DATETIME The difference is , If you have milliseconds , type TIMESTAMP Occupy 7 Bytes , and DATETIME Whether millisecond information is stored or not , All occupied 8 Bytes .

type TIMESTAMP The biggest advantage is that it can have a time zone attribute , Because it's essentially converted from milliseconds . If your business needs to correspond to different country time zones , So the type TIMESTAMP It's a good choice . Such as news business , Usually, users want to know the corresponding time of their country when this news is released , that TIMESTAMP It's a choice .Timestamp The value of the type field changes with the time zone of the server , Automatically convert to the corresponding time , To put it simply, in different time zones , The value of this field will be different when querying the same record .

TIMESTAMP Performance problems of

TIMESTAMP There are also potential performance issues .

Although the conversion from milliseconds to types TIMESTAMP What it needs CPU There are not many instructions , It's not a direct performance issue . But if you use the default operating system time zone , Each time the time is calculated through the time zone , To call the underlying system functions of the operating system __tz_convert(), And this function requires additional locking operations , To ensure that the operating system time zone is not changed at this time . therefore , When large-scale concurrent access , Due to the competition of hot resources , There are two problems ：

• Performance is not as good as DATETIME：DATETIME There is no time zone conversion problem .
• Performance jitter ： Massive concurrency , There is performance jitter .

In order to optimize the TIMESTAMP Use , Explicit time zones are recommended , Not the operating system time zone . For example, set the time zone as shown in the configuration file , Instead of using the system time zone ：

[mysqld]

time_zone = "+08:00"

Briefly summarize the advantages and disadvantages of these two data types ：

• DATETIME There's no time limit for storage , and TIMESTAMP The maximum storage time can only be up to ‘2038-01-19 03:14:07’
• DATETIME Without time zone attribute , Need front-end or server-side processing , But only in terms of saving data and reading data from the database , Better performance
• TIMESTAMP With time zone attribute , But each time you need to calculate time by time zone , There are performance issues with concurrent access
• Storage DATETIME Than TIMESTAMEP Take up more space

Numerical timestamps （INT）

A lot of times , We also use them int perhaps bigint The value of type is the time stamp to represent the time .

This kind of storage has Timestamp The type has some advantages , And using it for date sorting and comparison will be more efficient , Cross system is also very convenient , After all, it's just the stored value . The disadvantages are obvious , It's just that the readability of the data is so poor , You can't see the exact time directly .

If you need to view data in a certain period of time

select * from t where created_at > UNIX_TIMESTAMP('2021-01-01 00:00:00');

DATETIME vs TIMESTAMP vs INT, How to choose ？

Each has its own advantages , Let's make a simple comparison of these three ways ：

The date type	Occupancy space	Date format	Date range	Whether there is a time zone problem
DATETIME	8 byte	YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00 ~ 9999-12-31 23:59:59	yes
TIMESTAMP	4 byte	YYYY-MM-DD HH:MM:SS	1970-01-01 00:00:00 ~ 2037-12-31 23:59:59	no
INT	4 byte	All digital timestamp	1970-01-01 00:00:00 Time stamp after	no

TIMESTAMP And INT The essence is the same , But comparatively speaking, although INT Friendly to development , But yes. DBA And the data analysts are not friendly , Poor readability . therefore 《 High performance MySQL 》 The author recommends TIMESTAMP The reason is that its numerical value is more intuitive . Here is the original ：

As for the time zone , It can be transformed by the front end or the service , It doesn't have to be solved in the database .

summary

This article compares several of the most commonly used ways to store time , What I recommend most is DATETIME. For the following reasons ：

• TIMESTAMP Better readability than numeric timestamps
• DATETIME The maximum storage limit for is 9999-12-31 23:59:59, If you use TIMESTAMP, be 2038 We need to think about solutions
• DATETIME Because no time zone conversion is required , So performance ratio TIMESTAMP good
• If you need to store the time in milliseconds ,TIMESTAMP want 7 Bytes , and DATETIME 8 There's not much difference in bytes