What are the pits encountered by timestamp comparison query in mysql

This article mainly introduces the mysql timestamp comparison query encountered in which pits, the article is very detailed, has a certain reference value, interested friends must read it!

Timestamp compares the pits encountered by the query

When mysql is required to create a table, update_time is timestamp,create_time and datetime is required. Later, Ali's coding specification required that both should be of the datetime type.

The difference between timestamp and datetime is introduced in many places. Sometimes I wonder why JD.com requires update_time to be timestamp. Is it because it takes up less space? Or can only timestamp set the default value (on update current_timestamp)? The default value datetime can also be set. Later, Baidu learned that the default value of datetime support was only supported at 5.7. JD.com 's request may be due to the fact that the previous version of mysql is too low and that update_time can be updated automatically.

Now the same is required in a company, update_time is set to timestamp. And ended up in a pit. A colleague found a strange question: why does the date comparison query have no results, but directly execute the sql printed in the log to get the results? Why there is such an inconsistency, I have never encountered before. It's always exciting to solve problems.

I tried it locally, and it is true that there is no problem with the printed log, but it is the log that 'confuses' us, which makes people feel very strange. Take a look at the comparison field is update_time, which is of type timestamp. After the edification of Ali norms, it is keenly felt that it should be a matter of type. So my Baidu found that it was the problem of the time zone. Just add the serverTimezone=GMT%2B8 parameter after the database connection url. Of course, the other way is to use datetime, which can avoid a lot of holes.

Why is there such a problem? This is due to inconsistent time zones between the application server and the server deployed by mysql. This is why we see that there is no problem printing the log, but the result cannot be queried (the time seen in the log is the local time zone, but when the data is transferred to the mysql server, it is the time in another time zone)

Mysql's date also has the same problem.

Timestamp query scope problem

Date of timestamp type in MySQL, for example, the update time is 2020-05-26, and the query is update_time.