Logging time is often required in development, such as when a record was created or modified. There are many ways to store time in a database. For example, MySQL provides date types such as DATETIME, TIMESTAMEP, etc. We can also store the timestamp as INT directly, and some people can store the time as string directly.

So which is a better way to store time?

Do not use strings to store time types

This is a beginner’s mistake. It’s easy to just set the field to VARCHAR and store a string like “2021-01-01 00:00:00”. Of course, the advantage of doing this is relatively simple, fast.

This is not recommended, however, because there are two big problems with it:

  • A string takes up too much space

  • This stored field comparison is inefficient and can only be compared character by character without using the date API provided by MySQL

Date type in MySQL

The common DATE types in the MySQL database are YEAR, DATE, TIME, DATETIME, and TIMESTAMEP. DATETIME and TIMESTAMEP are suitable because they need to be accurate to the second.

DATETIME

DATETIME is stored in the format yyyY-MM-DD HH:MM:SS and occupies a fixed 8 bytes.

As of MySQL 5.6, the DATETIME type supports milliseconds. N in DATETIME(N) indicates the precision of milliseconds. For example, DATETIME(6) indicates that six bits of millisecond value can be stored.

TIMESTAMEP

TIMESTAMP Indicates the number of milliseconds since 1970-01-01 00:00:00. In MySQL, because type TIMESTAMP takes up 4 bytes, its storage time limit can only be ‘2038-19 03:14:07’.

Starting with MySQL 5.6, the TIMESTAMP type also supports milliseconds. Unlike DATETIME, where the type TIMESTAMP takes up 7 bytes with milliseconds, DATETIME takes up 8 bytes whether or not it stores millisecond information.

The biggest advantage of the type TIMESTAMP is that it can have a time zone attribute, as it is essentially converted from milliseconds. If your business needs to correspond to different country time zones, the type TIMESTAMP is a good choice. For example, in the business of news, users usually want to know the corresponding time of their country when this news is released, so TIMESTAMP is an option. Timestamp type field value will change with the server time zone changes, automatically converted to the corresponding time, said simple point is in different time zones, query to the same record the value of this field will be different.

TIMESTAMP performance issues

TIMESTAMP also has potential performance issues.

Although converting from milliseconds to the type TIMESTAMP itself does not require many CPU instructions, this does not present an immediate performance problem. However, if the default operating system time zone is used, the underlying operating system function __tz_convert() is called each time the time zone is calculated, which requires additional locking to ensure that the operating system time zone has not changed. Therefore, when large-scale concurrent access occurs, two problems arise due to hot resource competition:

  • Inferior performance to DATETIME: DATETIME does not have time zone conversion issues.

  • Performance jitter: Performance jitter occurs when a large number of concurrent requests occur.

To optimize the use of TIMESTAMP, it is recommended to use an explicit time zone rather than 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"
Copy the code

A brief summary of the pros and cons of the two data types:

  • DATETIME has no upper limit for storage, TIMESTAMP can only be stored up to ‘2038-19 03:14:07’

  • DATETIME does not have a time zone attribute and needs to be handled by the front end or server side, but it is better to save and read data only from the database

  • TIMESTAMP has a time zone attribute, but each time you need to calculate the time by the time zone, concurrent access will have performance problems

  • Storing DATETIME takes up a bit more space than TIMESTAMEP

Numeric timestamp (INT)

A lot of times, we’ll also use a value of type int or BigInt which is a timestamp.

This storage mode has some advantages of Timestamp type, and use it for date sorting and comparison operation efficiency will be higher, cross system is also very convenient, after all, only store the value. The downside is that the readability of the data is so poor that you can’t see the time directly.

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');
Copy the code

DATETIME vs TIMESTAMP vs INT

Each approach has its own advantages. Here is a simple comparison of these three approaches:

TIMESTAMP is essentially the same as INT, but in comparison, although INT is development-friendly, it is not friendly to DBAs and data analysts and is not readable. So the reason why the authors of High Performance MySQL recommend TIMESTAMP is that its numeric representation of time is more intuitive. Here’s the transcript:

As for the time zone problem, it can be converted from the front end or the service, not necessarily in the database.

conclusion

This article compared several of the most commonly used ways to store time, and DATETIME is the one I recommend. The reasons are as follows:

  • TIMESTAMP is more readable than numeric timestamps

  • The upper limit for storing DATETIME is 9999-12-31 23:59:59. If TIMESTAMP is used, a solution needs to be considered in 2038

  • DATETIME performs better than TIMESTAMP because it does not require a time zone conversion

  • If you need to store the time in milliseconds, TIMESTAMP takes 7 bytes, not much different from DATETIME’s 8 bytes

Recommended reading

Technology selection: Why do we choose Flink for batch processing

Redis stores object information using Hash or String