When Mysql builds a table, date-Time type selection

The date and time types supported by MySQL (5.5) are: DATETIME, TIMESTAMP, date, hour, year.

several types are compared as follows :

Date Time Type	Occupy space	Date format	Minimum value	Maximum Value	A value of 0 indicates
Datetime	8 bytes	YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00	9999-12-31 23:59:59	0000-00-00 00:00:00
TIMESTAMP	4 bytes	YYYY-MM-DD HH:MM:SS	19700101080001	At some 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 a combination of date and time, and the year of the record (see the table above) is longer than that of a date. You can use the DATETIME type if there is such a requirement in the actual application.

TIMESTAMP

• timestamp is used to denote a day and a minute, but the recorded year (see table above) is relatively short.
• TIMESTAMP is associated with the time zone and is more reflective of the current time. When the date is inserted, it is converted to the local time zone before being stored, and when the date is queried, the date is converted to the local time zone and then displayed. So people in different time zones see the same time as   is not the same.
• The first timestamp column is automatically set to system time (Current_ TIMESTAMP). When you insert or update a row but do not explicitly assign a value to the TIMESTAMP column, it is 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.
• timestamp are greatly affected by the Mysql version and the server Sqlmode.

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

DATE

Date is used to denote the date of year and month, and date can be used if the actual application value needs to be saved.

Time

time is used to represent seconds and minutes, and you can use time if you actually apply a value that needs to be saved.

Year

year is used to represent the year, which is 2 bits (preferably using 4-bit) and 4-bit format. The default is 4 bits. It is perfectly possible to save the year type with 1 bytes if the actual app only holds the years. Not only can save storage space, but also improve the efficiency of the table operation.

--------------------------------------------------------------------------------------------------------------- ------------------------------------------------

Each datetime type has a valid range of values, and if this range is exceeded, an error is made under the default Sqlmode and stored as 0 values (see table above).

When inserting or updating, datetime types allow "not strict" syntax, with DateTime as an example (other datetime types are identical):

• yyyy-mm-dd A string in HH:MM:SS or YY-MM-DD HH:MM:SS format. Any symbol can be used as a spacer for a date part or time part. For example: "14-06-18 14:54:10", "14*06*18 14.54.10", "14+06+18 14=54=10" are equivalent. For string values that contain datetime, you do not need to specify a two-digit number if the month, day, hour, minute, and second values are less than 10. For example: "2014-2-3 2:3:6", "2014-02-03 02:03:06" is equivalent.

• yyyymmddhhmmss or   yymmddhhmmss format string. If a string is legal for a datetime type, it can be interpreted as a datetime type. For example: "20140618145410" and "140618145410" will be interpreted as "2014-06-18 14:54:10 ", but "20140618145480 " is illegal (the number of seconds is illegal) and will be interpreted as "0000-00-00 00:00:00".

• Yyyymmddhhmmss orThe number in the YYMMDDHHMMSS format. If the number is legal for the datetime typecan be interpreted as a date-time type. For example:"20140618145410" and "140618145410" will be interpreted as "14-06-18 14:54:10". The length of the value should be 6, 8, 12, 14. If the numeric length is 8 or 14 bits long, it is assumed to be in YYYYMMDD or yyyymmddhhmmss format. If the value is 6 or 12 bits long, it is assumed to be in YYMMDD or YYMMDDHHMMSS format.

End