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

Can mysql handle dates? This problem may be often seen in our daily study or work. I hope you can gain a lot from this question. The following is the reference content that the editor brings to you, let's take a look at it!

Mysql can do date processing; it can use CURDATE (), NOW (), YEAR (), MONTH (), DAY (), HOUR (), MINUTE (), SECOND () and other built-in functions to do date processing, such as "SELECT CURDATE ()" to get the current date.

A recent project requires a lot of sql statements to be written. It involves a lot of time-related processing, so let's make a simple summary now.

1. Time format: in this project, perhaps by different people to establish the database table structure, the format of the time step is unified, some tables store long type timestamps, some tables store dates, some tables store time, while the format is 20171225, 2017-12-25 and so on, it is not uniform at all. Doing so will add some additional workload to the project and give rise to some problems. For example, when writing background code, the time needs to be processed to adapt to the time format of the database; in the winning joint query, the time format is not uniform, and the time format of sql statements will be converted, resulting in bloated sql statements. Therefore, the time format should be consistent in the same project. Personally, I think the timestamp of long type and the yyyy-MM-dd HH:mm:ss of String type are better.

The timestamp of the long type is because it is easy for the background java code to convert the time of the date type into a timestamp.

String type because, if we often use the time field, there is no need to convert Date type to String type, which is more intuitive and convenient to use.

two。 Common time statement

SELECT CURDATE (): 2020-10-7 / / date at this time SELECT NOW (): 2020-10-7 22:20:21 / / now time SELECT YEAR ('2020-10-7 22 purge 27Ze45'): 2020 / / year of acquisition time SELECT MONTH ('2020-10-7 22 purge 27 SELECT CURDATE 45'): 10 / month of acquisition time SELECT DAY ('2020-10-7 22 22 SELECT YEAR 27 SELECT YEAR'): 25 / / Number of days to get time SELECT HOUR ('2020-10-7 22 SELECT SECOND 2715 45'): 22 / hours to get time SELECT MINUTE ('2020-10-7 22 SELECT SECOND 2715 45'): 27 / minutes to get time SELECT DATE_SUB (NOW () INTERVAL 1 YEAR) / / subtract one year from the current time This statement can perform a variety of operations, subtract x years from a specified time: DATE_SUB ("2017-12-25", INTERVAL x YEAR), and subtract SELECT DATE_ADD such as x month/day/hour/minute/second (NOW (), INTERVAL 1 YEAR) / / ditto. Add one year SELECT CONCAT to the current time: 2017-12-12 / assembly string, which can be assembled into any time format. You can also use the above statement, for example: SELECT CONCAT (YEAR (NOW ()),'- 12-12'): 2017-12-12 SELECT UNIX_TIMESTAMP ('2017-12-25'): 1514131200 / / string converted to timestamp SELECT FROM_UNIXTIME (1514131200): 2017-12-25 00:00:00 / / timestamp converted to time SELECT DATE_FORMAT (' 201712-25 22 22 SELECT DATE_FORMAT,'% Y-%m-%d% HRV% iSELECT DATE_FORMAT% s') / / string can be converted into time format (date), or time format can be converted into string format. Thank you for reading! After reading the above, do you have a general understanding of mysql's ability to deal with dates? I hope the content of the article will be helpful to all of you. If you want to know more about the relevant articles, you are welcome to follow the industry information channel.

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.