MySQL time type selection

MySQL provides two very similar types, DATETIME and TIMESTAMP, for handling date and time. In most cases, both are OK, but in some cases, each has its own advantages and disadvantages.

DATETIME

DATETIME has a wider time span, from 1001 to 9999, with an accuracy of seconds. The storage format is to package the date and time and store them as an integer in the format of YYYYMMDDhhmmss. This time is independent of the time zone and takes up 8 bytes of storage space. By default, MySQL displays DATETIME in an ordered, unambiguous format, such as 2021-06-02 18:35:23. This is the ANSI standard date and time format.

TIMESTAMP

TIMESTAMP is a timestamp that stores the number of seconds since midnight Greenwich Mean Time (GMT) on January 1, 1970. Same as the timestamp on Unix systems. TIMESTAMP requires only 4 bytes to store, so it can represent a smaller time span, from 1970 to 2038. MySQL provides the FROM_UNIXTIME and UNIX_TIMESTAMP functions to complete the conversion between timestamps and dates.

After MySQL version 4.1, the format displayed by TIMESTAMP is similar to DATETIME, but the display of TIMESTAMP depends on the time zone. The MySQL server, operating system, and client connection all have time zone settings. Therefore, if times are stored from multiple time zones, the difference between TIMESTAMP and DATETIME can be significant. TIMESTAMP retains the time zone information when used, while DATETIME simply uses text to represent the time.

TIMESTAMP has additional features. By default, MySQL will use the current time to insert into the TIMESTAMP column if no value is specified, and will use the current time to update the field if no value is specified during update. Take the following test table as an example:

CREATE TABLE t_time_test (
    id INT PRIMARY KEY,
    t_stamp TIMESTAMP,
    t_datetime DATETIME
);

You can see that the default value given by MySQL is the current timestamp CURRENT_TIMESTAMP, and there is an ON UPDATE CURRENT_TIMESTAMP to indicate that it will be updated accordingly:

INSERT INTO t_time_test(id, t_datetime) VALUES
	(1, NULL), 
	(2, '2021-06-02 18:48:04'), 
	(3, NULL);

You can see that the t_stamp column is automatically filled with the current time.

This feature allows us to maintain the data update time field through the IoT program and let MySQL complete it.

How to choose

From the characteristics point of view, TIMESTAMP may be preferred to store time, which is more efficient than DATETIME. Some people use integers to store Unix timestamps, but this approach does not provide any benefit and integers require additional processing, so it is not recommended. However, there are some situations where you should be careful not to use TIMESTAMP to store time:

• Birthday: Birthdays are definitely earlier than 1970, which is beyond the range of TIMESTAMP
• Validity expiration date: The maximum time of TIMESTAMP is 2038. It is not suitable to store the validity expiration date of identity cards, business licenses, etc.
• Business survival time: In the Internet age, speed is important, and businesses develop (and die) quickly. If you want to be a long-lasting business, then your business will most likely still be operating in 2038, after all, it is already 2021. If you think your business won’t survive until 2038, that’s OK. Of course, if you are lucky enough to make it to 2038, please be sure to write down a to-do item: change the data table timestamp field type before January 1, 2038.
  

How to store millisecond time

Usually, you need to use BIGINT to convert the time into an integer for storage, or use a floating point number and use the fractional part to represent the time with a precision of less than one second. Both methods are feasible. Of course, at this time, application support is needed to do format conversion.

Conclusion

From a safety and security perspective, it is recommended to give priority to the DATETIME type. Although it will sacrifice a little performance compared to TIMESTAMP, the time range of TIMESTAMP is a flaw. Don't leave a hidden danger. When the year 2038 comes, your company may be a listed company, and programmers may encounter a flood of bugs without knowing why, resulting in a flash crash in the company's stock price! Then find out this programmer and discover that he is a former big shot in the company, a current shareholder, and you who have achieved financial freedom! Don’t you think it’s embarrassing?

The above is the details of the selection of MySQL time type. For more information about MySQL time type, please pay attention to other related articles on 123WORDPRESS.COM!