MySQL学习笔记4——时间函数

一、日期时间类型

时间函数就是用来处理时间的函数。时间，几乎可以说是各类项目中都会存在的数据，项目需求不同，我们需要的时间函数也不一样， 比如：

• 如果我们要统计一天之中不同时间段的销售情况，就要获取时间值中的小时值，这就会用到函数HOUR()；
• 要计算与去年同期相比的增长率，这就要计算去年同期的日期时间，会用到函数DATE_ ADD()；
• 要计算今天是周几、有没有优惠活动，这就要用到函数DAYOFWEEK()了；
• ……

1、获取日期时间数据中部分信息的函数

首先，我们来分析一下“统计一天中每小时的销售数量和销售金额”的这个需求。

先创建三个表，基于这三个表的信息进行举例：

要统计一天中每小时的销售情况，实际上就是要把销售数据按照小时进行分组统计。那么，解决问题的关键，就是把交易时间的小时部分提取出来。这就要用到MySQL的日期时间处理函数EXTRACT ()和HOUR () 了。
为了获取小时的值，我们要用到EXTRACT()函数。EXTRACT (type FROM date)表示从日期时间数据"date" 中抽取"type"指定的部分。

SELECT
	EXTRACT(HOUR FROM b.transdate) AS 时段,
	SUM(a.quantity) AS 数量,
	SUM(a.salesvalue) AS 金额
FROM
	demo.transactiondetails a
		JOIN
	demo.transactionhead b ON (a.transactionid = b.transactionid)
GROUP BY EXTRACT(HOUR FROM b.transdate)
ORDER BY EXTRACT(HOUR FROM b.transdate);

查询的过程是这样的：

1. 从交易时间中抽取小时信息: EXTRACT(HOUR FROM b.transdate)；
2. 按交易的小时信息分组；
3. 按分组统计销售数量和销售金额的和；
4. 按交易的小时信息排序。

这个查询，也可以通过使用日期时间函数HOUR()来达到同样的效果。HOUR (time) 表示从日期时间"time"中，获取小时部分信息。

SELECT
	HOUR(b.transdate) AS 时段,		--改写为HOUR()函数
	SUM(a.quantity) AS 数量,
	SUM(a.salesvalue) AS 金额
FROM
	demo.transactiondetails a
		JOIN
	demo.transactionhead b ON (a.transactionid = b.transactionid)
GROUP BY HOUR(b.transdate)			--改写为HOUR()函数
ORDER BY HOUR(b.transdate);			--改写为HOUR()函数

除了获取小时信息，我们往往还会遇到要统计年度信息、月度信息等情况，MySQL也提供了支持的函数。这些函数的使用方法和提取小时信息的方法一样。

YEAR (date)   :获取date中的年。
MONTH (date)  :获取date中的月。
DAY (date)    :获取date中的日。
HOUR (date)   :获取date中的小时。
MINUTE (date) :获取date中的分。
SECOND (date) :获取date中的秒。

2、计算日期时间的函数

我先来介绍2个常用的MySQL的日期时间计算函数：

• DATE_ ADD (date, INTERVAL表达式type)：表示计算从时间点"date" 开始，向前或者向后一段时间间隔的时间。“表达式”的值为时间间隔数，正数表示向后，负数表示向前，“type” 表示时间间隔的单位(比如年、月、日等)。
• LAST DAY (date)：表示获取日期则间"date"所在月份的最后一天的日期。

-- 今天是2024-04-16
SELECT DATE_ADD('2024-04-16',INTERVAL - 1 YEAR);
-- 结果返回“2023-04-16”

SELECT DATE_ADD(DATE_ADD('2024-04-16', INTERVAL -1 YEAR), INTERVAL -1 MONTH);
-- 结果返回“2023-03-16”

SELECT LAST_DAY(DATE_ADD('2024-04-16',INTERVAL - 1 YEAR));
-- 结果返回“2023-04-30”

3、其他日期时间函数

这时就要用到其他日期时间函数了，主要包括：

• CURDATE()
• DAYOFWEEK()
• DATE_ FORMAT
• DATEDIFF()
• ……

CURDATE () ：获取当前的日期。日期格式为"YYYY-MM-DD" ，也就是年月日的格式。

DAYOFWEEK (date)：获取日期"date" 是周几。1表示周日，2表示周一，以此类推，直到7表示周六。

总结：