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
DatetimeTake 'yyyy-MM-DD
Hh: mm: ss' format to retrieve and display datetime values. Supported range: '2017-01-01
00:00:00 'to '2017-12-31 23:59:59'
TimestampThe value cannot be earlier than 1970 or later than 2037
- Storage
Timestamp
-
- 4-byte storage (Time Stamp value is stored in 4 bytes)
- The value is saved in UTC (it stores the number of milliseconds)
- Time zone conversion: during storage, the current time zone is converted, and then retrieved to the current time zone.
Datetime
- 8-byte Storage)
- Actual storage format (just stores what you have stored and retrieves the same thing which you have stored .)
- It has nothing to deal with the timezone and conversion .)
Instance comparison
Now let's make a time zone impact on them.
- Insert a data insert into 't8 'values (now (), now ());
- Change the client Time Zone (East 9, Japan time zone ).
- The inserted data is displayed again and changed. The timestamp type data is increased by 1 hour.
Next we will discuss other timestamp attributes.
- 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
Reference http://dev.mysql.com/doc/refman/5.0/en/timestamp.html