MySQL之MySQL函数

1、DATE_SUB() 
函数从日期减去指定的时间间隔。
mysql中内置函数date_add和date_sub能对指定的时间进行增加或减少一个指定的时间间隔


语法
DATE_SUB(date,INTERVAL expr type)
date 参数是合法的日期表达式。expr 参数是您希望添加的时间间隔

eg：SELECT OrderId,DATE_SUB(OrderDate,INTERVAL 2 DAY) AS OrderPayDate
FROM Orders
py：select * from table where ip = 192.168.10.10 and DATE_SUB(CURDATE(), INTERVAL ' + str(days) +  ' DAY) <= date(date);

2、TO_DAYS()
mysql> SELECT TO_DAYS('2008-10-07'), TO_DAYS('08-10-07');
        -> 733687, 733687
//https://www.cnblogs.com/qcxdoit/p/7085799.html

3、DATEDIFF()
函数返回两个日期之间的天数
SELECT DATEDIFF('2008-12-30','2008-12-29') AS DiffDate   result:1

Example

1、根据某一时间字段查询最近七天的数据，并按天分组

SELECT
	DATE_FORMAT(published_date, '%Y-%m-%d' ) days,
	count(*) count 
FROM
(
SELECT
	nvd.* 
FROM
	nvd_jsons nvd
	LEFT JOIN main.vulnerability vul ON nvd.cve_id = vul.cve_id
	LEFT JOIN cve.cpes ON nvd.id = cpes.nvd_json_id
	LEFT JOIN cve.cwes ON nvd.id = cwes.nvd_json_id
	LEFT JOIN cwe.cwe_data ON cwes.cwe_id = cwe_data.cwe_id
	LEFT JOIN cve.cvss3 ON nvd.id = cvss3.nvd_json_id
	LEFT JOIN cve.descriptions des ON nvd.id = des.nvd_json_id
WHERE
  vul.cve_id IS NULL AND
	DATE_SUB(CURDATE(), INTERVAL 7 DAY ) <= date(nvd.published_date) ORDER BY nvd.published_date
	)  as n
	
	GROUP BY
	days;