Date time type Take up the space The date format

 The minimum value

 The maximum

 Zero said
DATETIME

 8 bytes

 YYYY-MM-DD HH:MM:SS 1000-01-01 00:00:00 The 9999-12-31 23:59:59 0000-00-00 00:00:00
TIMESTAMP

 4 bytes

 YYYY-MM-DD HH:MM:SS 19700101080001

 Sometime 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 a combination of DATE and TIME, and records a longer year (see table above). The DATETIME type can be used if such a requirement exists in a real-world application.Copy the code

1. The DATETIME column can be set to multiple columns. The default value can be null. 2. The DATETIME column cannot be set to default values. 3. The DATETIME column can be set to a default value by using a trigger, or by setting the DATETIME field to now() when inserting data. This can be done, especially when the latter is often used in program development.

TIMESTAMP

  • TIMESTAMP is used to indicate year, month, day, hour, minute, second, but the recorded year (as shown in the table above) is short. The TIMESTAMP column must have a default value, which can be 0000-00-00 00:00:00, but cannot be null.
  • TIMESTAMP is time-zone dependent and more reflective of the current time. When a date is inserted, it is converted to the local time zone before being saved. When querying a date, the system converts the date to the local time zone and displays it again. So people in different time zones see the same time differently.
  • The first TIMESTAMP column in the table is automatically set to the system time (CURRENT_TIMESTAMP). When a row is inserted or updated without explicitly assigning a value to the TIMESTAMP column, it is also automatically set to the current system time. If there is a second TIMESTAMP column in the table, the default value is set to 0000-00-00 00:00:00.
  • The attributes of TIMESTAMP are greatly affected by Mysql version and server SQLMode.
If the recorded date needs to be used by people in different time zones, use TIMESTAMP.Copy the code

Note: When creating a table, use datetime to create a table, and use timestamp to update a table.

DATE

DATE is used to indicate the DATE, month, and year. You can use DATE if the actual application value needs to be saved.Copy the code

TIME

TIME is used to represent the minute and second. You can use TIME if the actual application value needs to save the minute and second.Copy the code

YEAR

YEAR is used to represent a YEAR, and YEAR has two bits (preferably four) and four bit formats. The default is 4 bits. In practice, if you only store years, storing the YEAR type with 1 bytes is perfectly fine. It not only saves storage space, but also improves table operation efficiency.Copy the code

This article is published by OpenWrite!