Pitfalls and solutions encountered in MySQL timestamp comparison query

Pitfalls encountered in timestamp comparison queries

I remember that JD.com required MySQL to set update_time as timestamp and create_time as datetime when creating a table. Later, Alibaba's coding standards required that both must be of datetime type.

The difference between timestamp and datetime is introduced in many places. Sometimes I wonder why JD.com requires update_time to be a timestamp? Is it because it takes up less space? Or is it only possible to set a default value for timestamp (on update current_timestamp)? Can't the default value datetime also be set? Later I searched on Baidu and found out that datetime support for setting default values ​​was only available in 5.7. JD.com may have such a requirement because the MySQL version used before was too low, and it also required update_time to be automatically updated.

Now a company also requires this, update_time is set to timestamp. As a result, I encountered a pit. A colleague found a very strange problem: why does the date comparison query have no results, but directly executing the SQL printed in the log can query the results? ? Why does this inconsistency occur? I have never encountered this before. Solving problems is always exciting.

I tried it locally and it was indeed the case. There was no problem with the printed log, but it was the log that 'confused' us and made us feel very strange. I checked and found that the field being compared is update_time, which is of timestamp type. After being influenced by Alibaba's standards, I keenly felt that it should be a problem of type. So I searched on Baidu and found that it was a time zone problem. Just add the serverTimezone=GMT%2B8 parameter after the database connection URL. Of course, another way is to use datetime, which can avoid many pitfalls.

Why does this problem occur? This is because the time zone of the application server and the server where MySQL is deployed are inconsistent. This is why we see no problem with the print log, but no query results (the time seen in the log is the time zone of the local machine, but when the data is transferred to the MySQL server, it is the time in another time zone)

MySQL date also has this problem. . .

Timestamp query range problem

In MySQL, for example, if the update time is 2020-05-26, the query is update_time <= 2020-05-26, which cannot be found. It needs to be converted to DATE_FORMAT(info.up_time,'%Y-%m-%d') <= '2020-05-26'. The specific reason is unknown and needs further study.

The above is my personal experience. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM.