The difference between time type datetime,timestamp and int in MySQL

In MySQL storage time, we can use the DateTime format, timestamp format, can also be used in int format. So what should we think about when we design?

First of all, I think we should understand how these formats are, and then look at their differences so that we can think about when to use the right format.

The datetime format, by default, is "Yyyy-mm-dd HH:MM:SS", which represents 19 characters from 1000-01-01 00:00:00-9999-12-31 23:59:59.

The timestamp format is also ' Yyyy-mm-dd HH:MM:SS ', which differs from DateTime in that its year value range is 1970-2037.

The format of INT is the form of integers, it can control the number of bits, generally we set to 10 bits on it.

So when to use DateTime, when the format to be displayed is fixed, that is, the date of the month and minute, and so on, the way, timestamp, although the range of the datetime is small, but generally we think his range is big enough, In addition, it can be set to update the time, automatically modify the value, that is, when we modify the database, such as INSERT or UPDATE, this field when we set the

CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,时，它就会自动填入当前的时间。

Then when the use of int, originally int should not be the time format, but we have a lot of programming practice found that we usually in PHP programming, often need to format display time, such as only display date, if the database is an int type of time, Then we can directly use the PHP built-in function date () to convert, very convenient, and for time comparison is also more easy, because it is an int type, you can compare the size of the time can be compared directly.

The difference between time type datetime,timestamp and int in MySQL