Comparison of datetime and timestamp in MySQL

MySQL is used to represent the time of the three types of date, DateTime, timestamp (if the int is counted, four kinds) is more easily confused, the following comparison of these three types of similarities and differences

Same point

Can be used to represent time
are presented as strings

Different points

1. As the name implies, date only represents the ' yyyy-mm-dd ' form of the dates, DateTime represents the ' Yyyy-mm-dd HH:mm:ss ' form of the date plus time, timestamp is the same as the datetime display form.
2.date and DateTime can represent a time range of ' 1000-01-01 ' to ' 9999-12-31 ', timestamp because of the 32-bit int 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 have stored a value of type timestamp, you have modified the MySQL time zone, and when you read this value you will get an incorrect time. This situation does not occur 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. Except that date is reserved to days, datetime and timestamp are reserved to seconds, and milliseconds are ignored.

Time format

MySQL provides a more relaxed time string format for adding and deleting changes. Refer to ISO time format, generally used to write ' 2013-06-05 16:34:18 '. But you can also write ' 13-6-5 ', but it is easy to confuse, such as MySQL will also be ' 13:6:5 ' also as the month and day processing, and when the ' 13:16:5 ' This form, the MySQL is considered to be incorrect format, will give a warning, and then deposit the value of the database 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 a kind of superfluous.

In short, status quo, using ' yyyy-mm-dd HH:mm:ss ' format is always not wrong

Comparison of datetime and timestamp in MySQL