Same
Display
The display format of the TIMESTAMP column is the same as that of the DATETIME column. In other words, the display width is fixed to 19 characters and the format is YYYY-MM-DD HH: MM: SS.
Different
Range
Datetime retrieves and displays DATETIME values in 'yyyy-MM-DD HH: MM: ss' format. Supported range: '2017-01-01 00:00:00 'to '2017-12-31 23:59:59' TIMESTAMP value cannot be earlier than 1000 or later than 9999
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 .)
Instance comparison
Now let's make a time zone impact on them.
1. insert a data insert into 't8 'values (now (), now ());
2. Change the client Time Zone (East 9, Japan time zone ).
3. The inserted data is displayed again, and changed. The timestamp type data is increased by 1 hour.
Next we will discuss other timestamp attributes.
1. Whether null is null
The default value of timestamp is "not null by DEFAULT". It is invalid if you define "ts TIMESTAMP default NULL. You can specify null and "ts timestamp null". In this case, you can change the default value in the add statement.
Ts2 timestamp null default 0,
Ts3 timestamp null default CURRENT_TIMESTAMP
Default (only one column in a table can be selected)
Default CURRENT_TIMESTAMP
Default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
On update CURRENT_TIMESTAMP
On update can be found at 2