Details determine success or failure-Time Storage in mysql (datetime, timestamp, int),-mysqltimestamp

Details determine success or failure-Time Storage in mysql (datetime, timestamp, int),-mysqltimestamp

To store time in mysql, we can use datetime, timestamp, or int format. So what should we consider when designing?

First, 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 correct format.

DATETIME format, the default is "YYYY-MM-DD HH: MM: SS", represented by 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'. Unlike DATETIME, the value range of the year is 1970-2037.

The INT format is an integer. It can control the number of digits. Generally, we can set it to 10 digits.

When Will DATETIME be used? When the format to be displayed is relatively fixed, that is, the format of year, month, day, hour, minute, and second will be used. Although the timestamp is smaller than the datetime range, however, we generally think that the range is large enough. In addition, it can also be set to automatically modify the value when it is updated, that is, when we modify the database, such as insert or update, when we set this field

CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, which is automatically filled in with the current time.

So when should we use int? int should not be in the time format. However, after a lot of programming practices, we often need to format the display time in php programming, for example, to display only the year, month, and day, if the database stores int-type time, we can directly use the php built-in function date () for conversion, which is very convenient, in addition, it is easier to compare the time, because it is of the int type, you can directly compare the size to compare the time.