Date and Time type

MySQL provides several time and date types, including date and time types that are datetime, date, TIMESTAMP, hour, and year. An overview of these time and date types is as follows:

DATE

The date type is used when you only need a date value, and there is no time section. MySQL retrieves and displays the date value in ' YYYY-MM-DD ' format, supported by ' 1000-01-01 ' to ' 9999-12-31 '.

Storage requirements: 3 bytes

Time

Value range: " -838:59:59" to "838:59:59"

You can specify the time value in multi-medium format:

• A string as the ' HH:MM:SS ' format. The "loose" syntax is allowed-any punctuation mark can be used as a delimiter for the time part, for example, ' 10:11:12 ' and ' 10.11.12 ' are equivalent.
• As a string without the delimiter ' HHMMSS ' format, if it is interpreted as a time. For example, ' 101112 ' is understood as ' 10:11:12 ', but ' 109712 ' is illegal (it has meaningless minute parts) and becomes ' 00:00:00 '.
• As a number in the HHMMSS format, if it can be interpreted as a time. For example, 101112 is understood as ' 10:11:12 '.

Storage requirements: 3 bytes

Datatime

A datetime type is used when you need a value that contains both date and time information. MySQL retrieves and displays the datetime value in ' yyyy-mm-dd HH:MM:SS ' format, supported by ' 1000-01-01 00:00:00 ' to ' 9999-12-31 23:59:59 '. ("support" means that although earlier values may work, there is no guarantee that they can.) ）

Storage requirements: 8 bytes

timestamp[(M)]

The timestamp column type provides a type that you can use to automatically mark an INSERT or update operation with the current date and time. If you have more than one timestamp column, only the first one is automatically updated. The timestamp value can be from the beginning of one 1970 to 2037, with a precision of one second and its value displayed as a number.

The automatic update of the first timestamp column occurs under any of the following conditions:

• The column is not explicitly specified in an INSERT or load DATA infile statement.
• The column is not explicitly specified in an UPDATE statement and some additional columns change values. (Note An update setting that lists the values it already has, which will not cause the timestamp column to be updated, because if you set a list as its current value, MySQL ignores the changes for efficiency.) ）
• You explicitly set the timestamp column to be null.

Storage requirements: 4 bytes

year[(M)]

Range of values: 1901 to 2155

The year type is a valid 1-byte type representing the years. MySQL retrieves and displays the year value in yyyy format, which ranges from 1901 to 2155. If you only want to save the date, then year is more efficient than other types such as Samllint.

You can specify year values in a variety of formats, either with 4 as a character, or 4 as a string, of course within the range of 1901 to 2155.

One advantage of year is that you can specify a 2-bit string in the ' 00 ' to ' 99 ' range or a value in the ' 00 ' to ' 69 ' and ' 70 ' to ' 99 ' ranges to be transformed to a year value in the range 2000 to 2069 and 1970 to 1999.

Storage requirements: 1 bytes

MySQL itself Y2K (Year 2 Kilo) is safe, but the input value presented to MySQL may not be. Any input that contains a 2-bit year value is two-justified, because century is unknown. Such values must be interpreted in 4-bit form, because MySQL uses 4-bit storage years internally. For datetime, DATE, Timestamp, and year types, MySQL uses the following rules to interpret the value of the ambiguity:

The year value in the range 00-69 is transformed to 2000-2069.

The year value in the range 70-99 is transformed to 1970-1999.

Remember that these rules only provide reasonable guesses about the meaning of your data. If the heuristic rule used by MySQL does not produce the correct value, you should provide an input that contains no semantics for the 4-bit year value.

Date and Time type