Date, DateTime, and TimeStamp types in the MySQL database _ MySQL

The Date, DateTime, and TimeStamp types in the MySQL database are related. This article describes their features and how they are similar and different.

The DATETIME type is used when you need to include both the date and time information. MySQL retrieves and displays DATETIME values in 'yyyy-MM-DD HH: MM: SS' format, the supported range is '2017-01-01 00:00:00 'to '2017-12-31 23:59:59 '. ("Supported" means that although earlier values may work, they cannot be guaranteed .)

The DATE type is used when you only need the DATE value, and there is no time part. MySQL retrieves and displays the DATE value in 'yyyy-MM-DD 'format. the supported range is '2017-01-01' to '2017-12-31 '.

The TIMESTAMP column type provides a type that you can use to automatically mark INSERT or UPDATE operations with the current date and time. If you have multiple TIMESTAMP columns, only the first one is automatically updated.

The first TIMESTAMP column is automatically updated under any of the following conditions:

The column is not explicitly specified in an INSERT or load data infile statement.

Columns are not explicitly specified in an UPDATE statement and some other columns change values. (Note that setting an UPDATE column as its existing value does not cause the TIMESTAMP column to be updated, because if you set a column as its current value, MySQL ignores the changes for efficiency .)

You explicitly set the TIMESTAMP column as NULL.

The TIMESTAMP column except the first one can also be set to the current date and time, as long as the column is set to NULL, or NOW ().

By explicitly setting the expected value, you can set any TIMESTAMP column as a value different from the current date and time, even for the first TIMESTAMP column. For example, if you want a TIMESTAMP to be set to the current date and time when you create a row, but it will not change at any time after the row is updated, you can use this property:

Let MySQL set the column when the row is created, which will initialize it as the current date and time.

When you perform subsequent changes to other columns in the row, specify the current value of the TIMESTAMP column.

On the other hand, you may find that when a row is created and away from subsequent changes, it is easy to use a DATETIME column that you initialize with NOW.

The TIMESTAMP value can start from 1970 to 2037, and is precise to one second. its value is displayed as a number.

The TIMESTAMP value retrieved and displayed in MySQL depends on the display size format in the following table. The "complete" TIMESTAMP format is 14 bits, but the TIMESTAMP column can be created with a shorter Display size:

Column type display format
TIMESTAMP (14) YYYYMMDDHHMMSS
TIMESTAMP (12) YYMMDDHHMMSS
TIMESTAMP (10) YYMMDDHHMM
TIMESTAMP (8) YYYYMMDD
TIMESTAMP (6) YYMMDD
TIMESTAMP (4) YYMM
TIMESTAMP (2) YY

All TIMESTAMP columns have the same storage size, regardless of the display size. The most common display sizes are 6, 8, 12, and 14. You can specify an arbitrary display size at the table creation time, but the value 0 or greater than 14 is forced to 14. From 1 ~ The size of the odd value in the range of 13 is forced to be the next larger even number.

You can specify DATETIME, DATE, and TIMESTAMP values using any of the common format sets:

A string in 'yyyy-MM-DD HH: MM: SS' or 'YY-MM-DD HH: MM: SS' format. Allows a "loose" syntax-any punctuation can be used as a separator between the date part and the time part. For example, '98-12-31 11:30:45 'and '98. 12.31 11 + 30 + 45', '2017/31 11*30 * 45' are equivalent to '2017 11 ^ 30 ^ 45.

A string in 'yyyy-MM-DD 'or 'YY-MM-DD' format. A "loose" syntax is allowed. For example, '98-12-31 ', '98. 100', '192/31', and '123' are equivalent.