MySQL date type, date format, storage space, date range comparison.
Date type storage space Date format date range
------------ --------- --------------------- -----------------------------------------
DateTime 8 bytes Yyyy-mm-dd HH:MM:SS 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
Timestamp 4 bytes Yyyy-mm-dd HH:MM:SS 1970-01-01 00:00:01 ~ 2038
Date 3 bytes Yyyy-mm-dd 1000-01-01 ~ 9999-12-31
Year 1 bytes YYYY 1901 ~ 2155
When you create a table in MySQL, there are advantages to both datetime types: DateTime has a large date range, and timestamp occupies less storage space than half of the DateTime.
There are three default values for timestamp:
NULL
Not NULL DEFAULT Current_timestamp
Not NULL DEFAULT current_timestamp on UPDATE current_timestamp
which
Current_timestamp-Timestamp does not change when this record is updated
Current_timestamp on Update Current_timestamp-when this record is updated, the timestamp will change to the latest time
After setting the Current_timestamp default, inserting or updating records does not need to maintain the field, which is maintained entirely by MySQL, just like the AutoIncrement field, but if you use the DateTime field set above, you need to manually construct the time update , like now () and so on.
For current_timestamp on update current_timestamp, there is a special case where MySQL ignores the update operation if your updated field value (update) does not change, so the timestamp does not change. Also, if there are multiple current_timestamp on the Update current_timestamp column, then MySQL will only update the first one.
Datetime,timestamp,date,year comparison of time types in MySQL