Comparison between Datetime and Timestamp in Mysql, datetimetimestamp

Comparison between Datetime and Timestamp in Mysql, datetimetimestamp

In mysql, the three types of date, datetime, and timestamp (if int is used, four types) are easy to confuse. The following compares the similarities and differences between the three types of time.

Similarities

Can be used to indicate the time
Are displayed as strings

Differences

1. as the name suggests, date only represents the date in the form of 'yyyy-MM-DD ', datetime represents the date plus time in the form of 'yyyy-MM-DD HH: mm: ss', timestamp and datetime display form is the same.
2. the time range that date and datetime can represent is '2017-01-01 'to '2017-12-31'. timestamp is limited by 32-bit int type, it indicates the UTC time between '2017-01-01 00:00:01 'and '2017-01-19 03:14:07.
3. When mysql stores the timestamp type, it converts the time to UTC time, and then restores it to the current time zone when reading the data. If you have stored a value of the timestamp type and modified the mysql time zone, you will get an error time when you read the value again. This does not happen in date and datetime.
4. The timestamp type provides the automatic update function. You only need to set its default value to CURRENT_TIMESTAMP.
5. Except that date is retained to the day, datetime and timestamp are retained to the second, while millisecond is ignored.

Time Format

Mysql provides a relatively loose time string format for addition, deletion, modification, and query. Refer to the iso time format. Generally, it is used to writing '2017-06-05 16:34:18 '. However, you can also name it '13-6-5 ', but this is easy to cause confusion. For example, mysql also treats '13: 6: 5' as year, month, and day, while '13: 16: 5' is considered incorrect by mysql. A warning is given and the value stored in the database is '2017-00-00 00:00:00 '.

The Manual also specifically mentions a situation where the value of the current year is 0 ~ 69, mysql considers it as 2000 ~ 2069, and 70 ~ 99 is considered as 1970 ~ 1999. I feel like it is a superfluous thing.

In short, using the 'yyyy-MM-DD HH: mm: ss' format will always be fine.