Best Practices Guide for Storing Dates in MySQL

Preface

In daily development, it is often necessary to record time, such as the creation time and modification time of a record. There are many ways to store time in a 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?

Do not use strings to store time types

This is a mistake that beginners often make. They tend to directly set the field to VARCHAR type and store strings such as "2021-01-01 00:00:00". Of course, the advantage of doing this is that it is relatively simple and easy to get started.

However, it is strongly not recommended to do this because there are two major problems:

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

Date Types in MySQL

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

DATETIME

DATETIME is stored in the database in the format of YYYY-MM-DD HH:MM:SS, and occupies a fixed 8 bytes.

Starting from MySQL version 5.6, the DATETIME type supports milliseconds. The N in DATETIME(N) represents the millisecond precision. For example, DATETIME(6) means that 6 digits of milliseconds 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 TIMESTAMP type occupies 4 bytes, the upper limit of the time it can store is only '2038-01-19 03:14:07'.

Starting with MySQL version 5.6, the TIMESTAMP type also supports milliseconds. Unlike DATETIME, TIMESTAMP occupies 7 bytes if milliseconds are included, while DATETIME occupies 8 bytes regardless of whether milliseconds are stored.

The biggest advantage of the TIMESTAMP type is that it can have a time zone attribute, because it is essentially converted from milliseconds. If your business needs to correspond to different national time zones, then the TIMESTAMP type is a good choice. For example, in news services, users usually want to know the time in their own country when the news was published, so TIMESTAMP is an option. The value of the Timestamp type field will change with the server time zone and 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 issues

TIMESTAMP also has potential performance issues.

Although the conversion from milliseconds to type TIMESTAMP itself does not require many CPU instructions, this does not cause direct performance problems. However, if the default operating system time zone is used, each time the time is calculated using the time zone, the underlying operating system function __tz_convert() must be called, and this function requires additional locking operations to ensure that the operating system time zone has not been modified. Therefore, when large-scale concurrent access occurs, two problems will arise due to competition for hot resources:

• Performance is not as good as DATETIME: DATETIME does not have time zone conversion issues.
• Performance jitter: When there is massive concurrency, there is a performance jitter problem.

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"

Let's briefly summarize the advantages and disadvantages of these two data types:

• DATETIME has no upper limit on the time that can be stored, while TIMESTAMP can only store up to '2038-01-19 03:14:07'
• DATETIME does not have a time zone attribute and needs to be processed by the front-end or server, but it has better performance in terms of saving and reading data from the database only.
• TIMESTAMP has a time zone attribute, but the time needs to be calculated by the time zone each time, which may cause performance problems during concurrent access.
• Storing DATETIME takes up more space than TIMESTAMEP

Numeric timestamp (INT)

Often, 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 it is more efficient to use it for date sorting and comparison, and it is also convenient across systems because it only stores numerical values. The disadvantage is also obvious, that is, the readability of the data is too poor, and you cannot intuitively see the specific time.

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

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

DATETIME vs TIMESTAMP vs INT, which one should I choose?

Each method has its own advantages. Here is a simple comparison of these three methods:

TIMESTAMP and INT are essentially the same, but although INT is developer-friendly, it is not friendly to DBAs and data analysts and has poor readability. Therefore, the reason why the author of "High Performance MySQL" recommends TIMESTAMP is that its numerical value represents time more intuitively. Here is the original text:

As for the time zone issue, it can be converted once by the front end or service, and does not necessarily have to be resolved in the database.

Summarize

This article compares several of the most commonly used ways to store time, and my favorite is DATETIME. Here are the reasons:

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

This is the end of this article about storage time in MySQL. For more relevant MySQL storage time content, please search 123WORDPRESS.COM’s previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!