Comparison of datetime and timestamp in MySQL

In mysql, there are many time and date functions that I commonly use: datetime and timestamp. Although they are all called date periods, they are still different in terms of expression and range, next I will introduce it.

Comparison

Storage

TIMESTAMP

Storage of 1.4 bytes (Time stamp value is stored in 4 bytes)

2. Save the value in UTC format (it stores the number of milliseconds)

3. Time Zone conversion. during storage, the current time zone is converted, and then retrieved to the current time zone.

Datetime

1.8 bytes (8 bytes storage)

2. Storage in actual format (Just stores what you have stored and retrieves the same thing which you have stored .)

3. It has nothing to deal with the TIMEZONE and Conversion .)

Summary

Similarities

Can be used to indicate the time
Are displayed as strings

Differences

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.
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.
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.
 
The timestamp type provides the automatic update function. You only need to set its default value to CURRENT_TIMESTAMP.
Except that date is retained to the day, datetime and timestamp are retained to the second, while millisecond is ignored.