Shulou(Shulou.com)06/01 Report--

Preface

In the past, I always thought that I would use int to store it all the time. Recently, when I was developing a project, I saw datetime frequently, which caused some debate about which type to choose for the time-related fields in the MySQL database. So I did some simple research and looked at some blog and official documents. Finally, I make my own summary.

Type comparison int

The INT type is typically used to store Unix timestamps. Using a simple INT column to save Unix time is the most common way. With INT, you can ensure that the numbers you want to save can be quickly and reliably inserted into the table.

After it takes 4 bytes to build the index, the query is fast and the conditional range search can use the time function provided by between that cannot be provided by MySQL.

If you just store the Unix timestamp (and within its legal date range) and don't plan to use any date-based queries on it, I think it's okay to use INT. It performs simple numeric comparison queries very quickly because it is only dealing with simple numbers.

Conclusion: it is suitable for data tables that require a large number of time range queries, and the operation of MySQL time function, such as from_unixtime, is not supported in this field.

TIMESTAMP and DATETIME

Both Datetime and Timestamp store date and time information, while using human-readable date forms, such as "2018-01-01".

TIMESTAMP: a four-byte integer representing seconds UTC since the epoch ('1970-01-01 00 UTC)

TIMESTAMP: a four-byte integer, starting at 00:00:00 on 1970-01-01.

The core difference between Timestamp and Datetime lies in how MySQL represents this information internally: both are stored in binary rather than strings, but Timestamp (4 bytes) uses 1 byte less than Datetime (5 bytes, up from 8 bytes since MySQL 5.6.4) when representing the date / time portion. Both use extra space (1 to 3 bytes) when saving the millisecond portion.

DATETIME coding for non-fractional parts (after version 5.6.4):

1 bit sign (1 = non-negative, 0 = negative)

17 bits year*13+month (year 0-9999, month 0-12)

5 bits day (0-31)

5 bits hour (0-23)

6 bits minute (0-59)

6 bits second (0-59)

40 bits = 5 bytes

The 1 byte saved by Timestamp comes at a price: you can only store the time from '1970-01-01 00-0100-0000-01.00000000' to '2038-01-1903-14VR 07.999999'. And Datetime allows you to store any time from '1000-01-01 00-0000-0000000' to '9999-12-31 2323-59.999999'.

Another important difference is that MySQL uses the server's time zone to convert timestamp values to its UTC and other values before saving. When you get the value, it does the time zone conversion again, so you get back your "original" date / time value. The Datetime column is not changed by the database. No matter how the time zone is configured, the same value is saved and obtained each time.

After version 5.6, datetime began to use current time as the default, so there is no argument that timestamp is better suited than datetime as a type that needs to be changed with inserts and updates.

Conclusion: Datetime is the best choice in most scenarios for the reason that

It is human readable without any conversion. There will be no problems due to the time zone change. Only 1 byte more than its rivals to support a larger range of dates (from 1000 to 9999) summary

There is no perfect data type, just the most appropriate data type. There must be a reason for so many types to exist at the same time. If you understand the differences between different types, you can choose the most appropriate type.

Reference:

Parsing the selection of time and date types stored in MySQL what data type should you use to represent time in Date and Time Data Type RepresentationMySQL? Comparison of several data types of time Field in MySQL

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.