This is the second day of my participation in Gwen Challenge

MySQL stores dates and times in various types, such as YEAR and DATE. MySQL stores time types with accuracy to the second (MariaDB to the millisecond level). However, it can also be timed to the millisecond level. The choice of time type is not optimal, but depends on how the business needs to handle the storage of time.

MySQL provides two very similar DATETIME and TIMESTAMP types for handling dates and times. In most cases, both are OK, but in some cases they have their own advantages and disadvantages.

DATETIME

DATETIME spans are much larger, ranging from 1001 to 9999 years, with an accuracy of seconds. The storage format is YYYYMMDDhhmmss integer format. The 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 00:00 GMT on January 1, 1970. Same as the timestamp on Unix systems. TIMESTAMP requires only four bytes to be stored, so it can represent a smaller time span, from 1970 to 2038. MySQL provides FROM_UNIXTIME and UNIX_TIMESTAMP functions to convert time stamps to dates.

After MySQL version 4.1, TIMESTAMP is displayed in a similar format to DATETIME, however, TIMESTAMP is displayed depending on the time zone. MySQL server, operating system, and client connections are all set to the zone. Thus, TIMESTAMP and DATETIME differ considerably if the time is stored from multiple time zones. TIMESTAMP retains time zone information at the time of use, whereas DATETIME simply uses text to represent the time.

TIMESTAMP has additional features. By default, MySQL inserts the TIMESTAMP column with the current time when no value is specified, and updates the field with the current time when no value is specified.

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

The default value given by MySQL is CURRENT_TIMESTAMP, and ON UPDATE CURRENT_TIMESTAMP indicates that the CURRENT_TIMESTAMP will be updated with it:

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

You can see that the T_stamp column is automatically populated with the current time.

UPDATE `t_time_test` 
SET `t_datetime`='the 2021-06-02 19:00:00' WHERE id=1;
Copy the code

You can see that the T_stamp column of the column with id 1 is automatically updated to the current time.This feature allows the YMCA program to maintain the data update time field and leave it to MySQL to do so.

How to choose

In terms of features, you might prefer to use TIMESTAMP to store time, which is more efficient than DATETIME. There are also people who use integers to store Unix timestamps, which is not really beneficial and requires extra processing, so it’s not recommended. However, there are some situations where you need to be careful not to use TIMESTAMP to store time:

  • Birthdays: There will definitely be birthdays earlier than 1970, which will be out of TIMESTAMP range
  • Expiration time: the maximum time of TIMESTAMP is 2038, if used to store similar ID card expiration time, business license expiration time is not appropriate.
  • Business survival time: The Internet era pays attention to speed,The development ofQuick (death). If you’re going to be a business for the long haul, chances are you’ll still be in business in 2038. After all, it’s 2021. If you don’t think the business will make it to 2038, that’s fine. Of course, if you’re lucky enough to make it to 2038, be sure to write down a to-do list:Modify table timestamp field types by January 1, 2038.

How do I store millisecond time

Usually you need to use BIGINT to convert the time to integer storage, or you can use floating-point numbers with fractions representing sub-second times, either way. Of course, this time the application support to do format conversion.

conclusion

From the perspective of safety and security, it is recommended to choose DATETIME type first, although it will sacrifice a little performance compared with TIMESTAMP, but TIMESTAMP time range is hard, do not bury a hidden danger, until the real year 2038, your company may be a listed company, Programmers can get hit by a flood of bugs and not understand why, resulting in a stock price crash! Then find out this programmer, found is the company’s god, the current shareholder, has achieved financial freedom of you! Would you say awkward?