Select the date and time type when creating a Mysql table

Mysql (5.5) supports DATETIME, TIMESTAMP, DATE, TIME, and YEAR.

Comparison of several types is as follows:

Date and Time Type	Occupied Space	Date Format	Minimum value	Maximum Value	Zero value representation
DATETIME	8 bytes	YYYY-MM-DD HH: MM: SS.	1000-01-01 00:00:00	23:59:59, 9999-12-31	0000-00-00 00:00:00
TIMESTAMP	4 bytes	YYYY-MM-DD HH: MM: SS.	19700101080001	A time point in 2038	00000000000000
DATE	4 bytes	YYYY-MM-DD	1000-01-01	9999-12-31	0000-00-00
TIME	3 bytes	HH: MM: SS	-838: 59: 59	838: 59: 59	00:00:00
YEAR	1 bytes	YYYY	1901	2155	0000

 

 

 

 

 

 

 DATETIME

DATETIME is used to indicate the year, month, day, hour, minute, and second. It is a combination of DATE and TIME, and the recorded year (see the table above) is relatively long. If such a requirement exists in the actual application, the DATETIME type can be used.

 TIMESTAMP

• TIMESTAMP indicates the year, month, day, hour, minute, and second, but the recorded year (see the table above) is relatively short.
• TIMESTAMP is related to the time zone to better reflect the current time. When the date is inserted, it is converted to the local time zone before storage. When the date is queried, the date is converted to the local time zone before display. Therefore, people in different time zones may see different results at the same time.
• The first TIMESTAMP column in the table is automatically set to system time (CURRENT_TIMESTAMP ). When a row is inserted or updated, but the TIMESTAMP column is not explicitly assigned a value, it is automatically set to the current system time. If the table contains the second TIMESTAMP column, the default value is 0000-00-00 00:00:00.
• TIMESTAMP attributes are greatly affected by Mysql version and server SQLMode.

If the recorded date needs to be used by people in different time zones, it is best to use TIMESTAMP.

 DATE

DATE indicates the year, month, and day. If the actual application value needs to be saved, DATE can be used.

 TIME

TIME indicates the hour, minute, and second. If the actual application value needs to be saved, TIME can be used.

 YEAR

YEAR indicates the YEAR. YEAR has two digits (preferably four digits) and four digits. The default value is 4 characters. If the actual application only saves the YEAR, you can use 1 bytes to save the YEAR type. It not only saves storage space, but also improves the efficiency of table operations.

 

Certificate ---------------------------------------------------------------------------------------------------------------------------------------------------------------

 

Each date and time type has a valid value range. If the range is exceeded, an error is reported in the default SQLMode and stored as zero (see the table above.

 

When inserting or updating data, the DATETIME type allows "not strict" syntax. Take DATETIME as an example (Other Date and Time types are the same ):

• YYYY-MM-DD HH: MM: SS or YY-MM-DD HH: MM: SS format string. Any symbol can be used as the delimiter between the date part and the time part. For example, "14-06-18 14:54:10", "14*06*18 14.54.10", and "14 + 06 + 18 14 = 54 = 10" are equivalent. For string values that contain date and time, if the values of month, day, hour, minute, and second are less than 10, you do not need to specify two digits. For example, "02:03:06" and "" are equivalent.

• A string in the YYYYMMDDHHMMSS or YYMMDDHHMMSS format. If the string is valid for the datetime type, it can be interpreted as the datetime type. For example, "20140618145410" and "140618145410" will be interpreted as "14:54:10", but "20140618145480" is invalid (invalid seconds ), it will be interpreted as "0000-00-00 00:00:00 ".

• A number in the YYYYMMDDHHMMSS or YYMMDDHHMMSS format. If this number is valid for the datetime type, it can be interpreted as the datetime type. For example, "20140618145410" and "140618145410" will be interpreted as "14:54:10 ". The length of the value is 6, 8, 12, and 14. If the value length is 8 or 14 characters, it is assumed that it is in YYYYMMDD or YYYYMMDDHHMMSS format. If the value is 6 or 12 characters long, it is assumed that it is in YYMMDD or YYMMDDHHMMSS format.

 

 

End