Can mysql do date processing?

mysql can do date processing; it can use built-in functions such as CURDATE(), NOW(), YEAR(), MONTH(), DAY(), HOUR(), MINUTE(), SECOND(), etc. Do date processing, for example "SELECT CURDATE()" to get the current date.

#A recent project required writing a lot of SQL statements. It involves a lot of time-related processing, let’s make a brief summary now.

1. Time format: In this project, perhaps the database table structure was established by different people. The format of time is gradually unified. Some tables store long types. The timestamp, some tables store the date, some tables store the time, and the formats include 20171225, 2017-12-25, etc., which are not uniform at all. Doing so will add some extra workload to the project and will also cause some problems. For example, when writing background code, the time needs to be processed to adapt to the time format of the database; when performing joint queries to win, the time format is not uniform, and the time format of the SQL statement will be converted, resulting in bloated SQL statements. . Therefore, within the same project, the time format should remain consistent. Personally, I think the long type timestamp and the String type yyyy-MM-dd HH:mm:ss are better.

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

The String type is because if we often use the time field, we do not need to convert the Date type to the String type during application. It is more intuitive and convenient to use.

2. Commonly used time statements

SELECT  CURDATE()：2020-10-7　　//此时日期
SELECT  NOW() : 2020-10-7 22:20:21　　//此刻时间
SELECT  YEAR('2020-10-7 22:27:45'): 2020　//获取时间的年份
SELECT  MONTH('2020-10-7 22:27:45'): 10　//获取时间的月份
SELECT  DAY('2020-10-7 22:27:45')：25　　//获取时间的天数
SELECT  HOUR('2020-10-7 22:27:45')：22　　//获取时间的小时
SELECT  MINUTE('2020-10-7 22:27:45')：27　　//获取时间的分钟
SELECT  SECOND('2020-10-7 22:27:45')：45　　//获取时间的秒数

SELECT  DATE_SUB(NOW(),INTERVAL   1   YEAR)　　//在目前的时间减去一年，该语句可以进行多种操作，指定时间下减去x年：DATE_SUB("2017-12-25",INTERVAL x YEAR),同时可以减去 x month/day/hour/minute/second等
SELECT  DATE_ADD(NOW(),INTERVAL 1 YEAR)　　//同上，在目前的时间上加上一年
SELECT  CONCAT('2017','-12-12')：2017-12-12　　//组装字符串，可以组装成任何时间格式，也可以使用如上语句，例如：SELECT CONCAT(YEAR(NOW()),'-12-12') ：2017-12-12 ；
SELECT UNIX_TIMESTAMP('2017-12-25')：1514131200　　//字符串转换成时间戳
SELECT FROM_UNIXTIME(1514131200) ：2017-12-25 00:00:00　　//时间戳转化为时间
SELECT DATE_FORMAT('2017-12-25 22:23:01', '%Y-%m-%d %H:%i:%s'); 　　//字符串转化为时间格式（date），也可以时间格式转化为字符串格式

The above are some time-related statements that are commonly used in mysql.

Recommended tutorial: mysql video tutorial

The above is the detailed content of Can mysql do date processing?. For more information, please follow other related articles on the PHP Chinese website!