Difference between DATETIME, DATE, and TIMESTAMP in mysql

The difference between DATETIME, DATE, and TIMESTAMP in mysql is that the DATETIME, DATE, and TIMESTAMP types are similar. This article describes their characteristics and their similarities and differences. The DATETIME type can be used for values that need to contain both date and time information. MySQL retrieves and displays DATETIME types in 'yyyy-MM-DD HH: MM: ss' format. The supported range is '2017-01-0100:00:00 'to '2017-12-1000: 59: 59 '. ("Supported" means that even though earlier values may work, they cannot be guaranteed .) The DATE type can be used when a DATE value is required instead of a time part. MySQL retrieves and displays DATE values in 'yyyy-MM-DD 'format. The supported range is '2017-01-01 'to '2017-12-31 '. The TIMESTAMP column type provides a type through which you can automatically mark the Insert or Update operation with the date and time of the current operation. If a table has multiple TIMESTAMP columns, only the first one is automatically updated. The "complete" TIMESTAMP format is 14 bits, but the TIMESTAMP column can also use a shorter display size to create the most common Display sizes are 6, 8, 12, and 14. You can specify an arbitrary display size when creating a table, but the defined column length is 0 or greater than 14, and the column length is forcibly defined as 14. The column length ranges from 1 ~ The dimensions of odd values in the range of 13 are forced to be the next larger even number. For example, define the length of a field. Force the length of a field: TIMESTAMP (0)-> TIMESTAMP (14) TIMESTAMP (15)-> TIMESTAMP (14) TIMESTAMP (1)-> TIMESTAMP (2) TIMESTAMP (5)-> TIMESTAMP (6) All TIMESTAMP columns have the same storage size, using the complete precision of the specified period time value (14 bits) valid values are stored regardless of the display size. An invalid date will be forced to store 0 to automatically update the first TIMESTAMP column under any of the following conditions: the column value is not explicitly specified in an Insert or load datainfile statement. The column value is not explicitly specified in an Update statement, and other column values have changed. (Note: When a column value is set to its original value for an Update, this will not cause updates to the TIMESTAMP column, because if you set a column value to its current value, mySQL ignores updates to ensure efficiency .) Explicitly set the TIMESTAMP column with NULL. Other TIMESTAMP columns except the first column can be set to the current date and time, as long as the column is assigned NULL or NOW (). Any TIMESTAMP column can be set with a value different from the current operation date and time, which is achieved by explicitly specifying a value for this column. This applies to the first TIMESTAMP column. This selectivity is very useful. For example, if you want the TIMESTAMP column to save the current date and time when the record row is newly added, but this value does not change, whether or not to update the record row in the future: when the record row is created, let MySQL set the column value. This will initialize the column as the current date and time. When you update other columns of the record row, the TIMESTAMP column value is explicitly specified as its original value. On the other hand, you may find it easier to use the DATETIME column. When the new record row is created, the column is initialized with NOW () and will not be processed when the record row is updated. Example ):

Mysql> Create TABLE 'ta '(-> 'id' int (3) unsigned not null auto_increment,-> 'date1' timestamp (14) not null, -> 'date2' timestamp (14) not null,-> primary key ('id')->) TYPE = MyISAM; Query OK, 0 rows affected (0.01 sec) mysql> Insert INTO 'ta 'SET 'id' = 1; Query OK, 1 row affected (0.02 sec) # The first timestamp column value is not explicitly specified, this column value is set to the current time of insertion # other timestamp column values are not explicitly specified, MySQL considers that the inserted value is an invalid value, the column value is set to 0 mysql> Insert INTO 'ta's (2, NOW (), NULL); Query OK, 1 row affected (0.01 sec) mysql> Select * FROM 'ta '; + ---- + ---------------- + -------------- + | id | date1 | date2 | + ---- + ------------------ + ---------------- + | 1 | 20030503104118 | 00000000000000 | 2 | 20030503104254 | + ---- + ---------------- + ---------------- + 2 rows in set (0.00 sec) mysql> Update 'ta 'set' id' = 3 Where 'id' = 1; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 # When a record row is updated, the first timestamp column value will also be updated mysql> Update 'ta 'set 'id' = 2 Where 'id' = 2; query OK, 0 rows affected (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 0 # MySQL ignores this operation, the value of the first timestamp column will not be updated by mysql> Select * FROM 'Ta '; + ---- + ---------------- + -------------- + | id | date1 | date2 | + ---- + ------------------ + ---------------- + | 3 | 20030503104538 | 00000000000000 | 2 | 20030503104254 | + ---- + ---------------- + ---------------- + 2 rows in set (0.00 sec) mysql> Update 'ta 'set' id' = 1, 'date1' = 'date1' Where 'id' = 3; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 # The first timestamp column value is explicitly specified as its original value, which will not be updated by mysql> Select * FROM 'ta '; + ---- + ---------------- + -------------- + | id | date1 | date2 | + ---- + ------------------ + ---------------- + | 1 | 20030503104538 | 00000000000000 | 2 | 20030503104254 | + ---- + ---------------- + ---------------- + 2 rows in set (0.00 sec) * the above results are tested in MySQL 4.0.12.