A comparison of datetime and timestamp in Mysql _mysql

The three types of date, DateTime, and timestamp (four kinds) used to represent time in MySQL are easy to confuse, and the following compare the similarities and differences of these three types

Same point

Can be used to represent time
are displayed as strings

Different points

1. As the name suggests, date only represents the ' yyyy-mm-dd ' form of the date, DateTime represents the ' Yyyy-mm-dd HH:mm:ss ' form of the date plus time, timestamp and datetime display form.
2.date and DateTime can be expressed in the time range ' 1000-01-01 ' to ' 9999-12-31 ', timestamp due to the 32-bit int type limit, can represent ' 1970-01-01 00:00:01 ' to ' 2038-01-19 03:14:07 UTC time.
3.mysql converts the time to UTC time when storing the timestamp type and then reverts to the current time zone when it is read. If you store a value of timestamp type, and then modify the MySQL time zone, you will get the wrong time when you read the value again. This does not happen in date and DateTime.
The 4.timestamp type provides the ability to update automatically, and you only need to set its default value to Current_timestamp.
5. The datetime and timestamp are persisted to the second, but the milliseconds are ignored, except that date is reserved to days.

Time format

MySQL provides a more relaxed time string format for additions and deletions. Reference to ISO time format, generally used to write ' 2013-06-05 16:34:18 '. But you can also write ' 13-6-5 ', but this is easily confusing, such as MySQL will also be "13:6:5" as the year and the day processing, and when the ' 13:16:5 ' this form, is considered by MySQL is not the correct format, will give a warning, and then stored in the database value is ' 0000-00-00 00:00:00 '.

The manual also specifically mentions a situation, that is, when the value is 0~69, MySQL is considered 2000~2069, and 70~99 is considered 1970~1999. I feel like a kind of lily.

In short, status quo, using the ' yyyy-mm-dd HH:mm:ss ' format is always infallible.