Use the MySQLDate/Time type

Last time I had some summary about MySQL, but wiLdGoose said last night that he had encountered the same problem but could not solve it. After various assumptions and judgments, I finally found that ZendStudio had a time zone configuration problem (I am sweating ). During the discussion with him, I also talked a lot about MySQL details. it is better to record them as a memo. This article also persuaded wiLdGoose to use it. I once had the same team with him, so I am very familiar with his "cleanliness" practices. I also agree with many of his views; however, it is hard to understand that the Date/Time type is always avoided during database design. The method is to set the time-related fields to the INT (10) type and store them with UNIX timestamps. I personally disagree with this practice:

First, there are different types of operations. the "time calculation" similar to wiLdGoose is essentially an operation between integers (and this integer is very large and the length is 10 ). Furthermore, setting the timestamp to VARCHAR (10) makes efficiency problems self-evident.

This article describes the efficiency of time computing, integer computing, and even string computing.

Second, there are logical operation problems. This is an advantage of the time type, especially for projects that require high precision. For example, if we need "data from the previous week" and "data from every Monday since database creation", the complexity of such operations can be imagined using wiLdGoose's practices.

Finally, it is intuitive and not intuitive. we can understand that our brain will not directly convert this large string of timestamps into date formats. In comparison, the direct use of the time type is much more intuitive (it itself is the time format ).

My current team is still using a similar method. I have been in dispute over similar technical details for a long time. However, due to my position and decision-making, the team still cannot accept my opinion. I am sorry.

MySQL is positioned as a simple and fast DBM that can be quickly controlled, but on the other hand, it is easy to cause a situation that will not go further. For this purpose, we should pay more attention to the database design details of each item. new features are constantly added to a product until the end of the process is application-oriented.

Finally, the MySQL official time and date function manual is attached.