Best practices for storing time in MySQL

In normal development, it is often necessary to record time, for example, to record the creation time and modification time of a record. There are many ways to store time in the database. For example, MySQL itself provides date types, such as DATETIME, TIMESTAMEP, etc. We can also directly store timestamps as INT types, and some people directly store time as string types.

So which way of storing time is better?

Don't use string storage time type

This is an easy mistake for beginners. It is easy to directly set the field to VARCHAR type and store a string like "2021-01-01 00:00:00". Of course, the advantage of this is that it is relatively simple and quick to get started.

However, it is strongly not recommended to do so, because it has two major problems:

• Strings take up a lot of space
• The comparison efficiency of the fields stored in this way is too low, it can only be compared character by character, and the date API provided by MySQL cannot be used.

Date types in MySQL

Common date types in MySQL databases are YEAR, DATE, TIME, DATETIME, and TIMESTAMEP. Because the date is generally required to be accurate to the second, the more suitable ones are DATETIME and TIMESTAMEP.

DATETIME

The format of DATETIME stored in the database is: YYYY-MM-DD HH:MM:SS, which occupies 8 bytes fixedly.

Starting from MySQL 5.6, the DATETIME type supports milliseconds, and the N in DATETIME(N) represents the precision of milliseconds. For example, DATETIME(6) means that a 6-digit millisecond value can be stored.

TIMESTAMEP

TIMESTAMP actually stores the number of milliseconds from '1970-01-01 00:00:00' to the present. In MySQL, because the type TIMESTAMP occupies 4 bytes, the upper limit of its storage time can only reach '2038-01-19 03:14:07'.

Starting from MySQL 5.6, the type TIMESTAMP can also support milliseconds. The difference with DATETIME is that if there are milliseconds, the type TIMESTAMP occupies 7 bytes, and DATETIME occupies 8 bytes regardless of whether the millisecond information is stored.

The biggest advantage of the type TIMESTAMP is that it can have time zone attributes, because it is essentially converted from milliseconds. If your business needs to correspond to different national time zones, then the type TIMESTAMP is a good choice. For example, for news services, users usually want to know the time of their own country when this news is released, so TIMESTAMP is a choice. The value of the Timestamp type field will change as the server time zone changes, and it will be automatically converted to the corresponding time. To put it simply, the value of this field will be different when the same record is queried in different time zones.

TIMESTAMP performance problem

TIMESTAMP also has potential performance issues.

Although the conversion from milliseconds to the type TIMESTAMP itself does not require many CPU instructions, this does not bring direct performance problems. However, if the default operating system time zone is used, every time the time is calculated through the time zone, the underlying system function __tz_convert() of the operating system must be called, and this function requires additional lock operations to ensure that the operating system time zone is not modified at this time. Therefore, when large-scale concurrent access, due to hot resource competition, two problems will arise:

• Performance is not as good as DATETIME: DATETIME does not have time zone conversion issues.
• Performance jitter: There is a performance jitter problem when massive concurrency occurs.

In order to optimize the use of TIMESTAMP, it is recommended to use an explicit time zone instead of the operating system time zone. For example, set the time zone explicitly 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 has no upper limit of the time stored, and the upper limit of the time stored by TIMESTAMP can only reach '2038-01-19 03:14:07'
• DATETIME has no time zone attribute and requires front-end or server-side processing, but it has better performance in terms of only saving and reading data from the database
• TIMESTAMP has a time zone attribute, but every time you need to calculate the time by the time zone, there will be performance problems during concurrent access
• Storage of DATETIME takes up more space than TIMESTAMEP

Numerical Timestamp (INT)

Many times, we also use int or bigint type values, that is, timestamps to represent time.

This storage method has some advantages of the Timestamp type, and the efficiency of operations such as date sorting and comparison using it will be higher, and it is also very convenient to cross-system, after all, it is just a stored value. The disadvantage is also obvious, that is, the readability of the data is too poor, and you cannot see the specific time intuitively.

If you need to view the 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 method has its own advantages. Here is a simple comparison of these three methods:

TIMESTAMP is essentially the same as INT, but in comparison, although INT is friendly to development, it is not friendly to DBAs and data analysts and has poor readability. So the reason why the author of "High Performance MySQL" recommends TIMESTAMP is that its numerical value represents time more intuitively. The following is the original text:

As for the time zone issue, a conversion can be done by the front-end or service here, and it does not have to be resolved in the database.

to sum up

This article compares several of the most commonly used methods of storing time, and I recommend DATETIME the most. The reasons are as follows:

• TIMESTAMP is more readable than numeric timestamp
• The storage limit of DATETIME is 9999-12-31 23:59:59. If TIMESTAMP is used, a solution needs to be considered in 2038
• DATETIME does not require time zone conversion, so its performance is better than TIMESTAMP
• If you need to store the time in milliseconds, TIMESTAMP requires 7 bytes, which is not much different from DATETIME 8 bytes

Recommended reading

technology selection: why did we choose Flink

Redis store object information using Hash or String