mysql的时间函数

mysql中的时间函数

　　一、EXTRACT(type FROM date)

　　EXTRACT（type FROM date）表示从日期时间数据“date”中抽取“type”指定的部分。

　　eg:

-- 按小时统计销售金额和销售数量
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（time）

　　HOUR（time）表示从日期时间“time”中，获取小时部分信息。

　　需要注意的是，EXTRACT() 函数中的“HOUR”表示要获取时间的类型，而 HOUR() 是一个函数，HOUR(time) 可以单独使用，表示返回 time 的小时部分信息。

　　上面的例子可以用HOUR()改写：

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中不止有hour（），还有其他的：

　　YEAR（date）：获取 date 中的年。

　　MONTH（date）：获取 date 中的月。

　　DAY（date）：获取 date 中的日。

　　HOUR（date）：获取 date 中的小时。

　　MINUTE（date）：获取 date 中的分。

　　SECOND（date）：获取 date 中的秒。

　　三、时间计算

　　1、DATE_ADD（date, INTERVAL 表达式 type）

　　DATE_ADD（date, INTERVAL 表达式 type）：表示计算从时间点“date”开始，向前或者向后一段时间间隔的时间。“表达式”的值为时间间隔数，正数表示向后，负数表示向前，“type”表示时间间隔的单位（比如年、月、日等）。

　　比如获取上一年的日期：

SELECT DATE_ADD('2023-12-10', INTERVAL - 1 YEAR);

　　还有稍微复杂点的例子，比如获取2023-12-10的上一年上个月的日期

SELECT DATE_ADD(DATE_ADD('2020-12-10', INTERVAL - 1 YEAR),INTERVAL - 1 MONTH);

　　2、LAST_DAY（date）

　　LAST_DAY（date）：表示获取日期时间“date”所在月份的最后一天的日期。

　　比如获取2023-12-10的当前12月的最后一天的日期

SELECT LAST_DAY('2023-12-10')

　　有些时候我们可能需要将两个结合下：

SELECT DATE_ADD(LAST_DAY(DATE_ADD(DATE_ADD('2023-12-10', INTERVAL - 1 YEAR),INTERVAL - 1 MONTH)),INTERVAL 1 DAY)

　　查询过程如下：

　　1）用 DATE_ADD 函数，获取到 2023 年 12 月 10 日上一年的日期：2022 年 12 月 10 日

　　2）获取 2022 年 12 月 10 日这个时间节点开始上个月的日期，这样做的目的是方便获取月份的起始时间

　　3）获取 2022 年 11 月 10 日这个时间点月份的最后一天，继续接近我们的目标：2022 年 12 月 01 日

　　4）计算 2022 年 11 月 30 日后一天的日期。

　　简单小结下：

　　我们可以用 DATE_ADD() 来计算从某个时间点开始，过去或者未来一个时间间隔的时间；通过 LAST_DAY() 函数，获得某个时间节点当月的最后一天的日期。借助它们，我们就可以获取从某个时间节点出发的指定月份的起始日期和截止日期。

　　除了 DATE_ADD()，ADDDATE()、DATE_SUB() 和 SUBDATE() 也能达到同样的效果。

　　ADDDATE()：跟 DATE_ADD() 用法一致；

　　DATE_SUB()，SUBDATE()：与 DATE_ADD() 用法类似，方向相反，执行日期的减操作。

　　3、其他的一些函数

　　CURDATE（）：获取当前的日期。日期格式为“YYYY-MM-DD”，也就是年月日的格式。

　　DAYOFWEEK（date）：获取日期“date”是周几。1 表示周日，2 表示周一，以此类推，直到 7 表示周六。

CASE DAYOFWEEK(CURDATE()) - 1 WHEN 0 THEN 7 ELSE DAYOFWEEK(CURDATE()) - 1 END AS 周几,

　　DATE_FORMAT()，它表示将日期时间“date”按照指定格式显示。

SELECT DATE_FORMAT("2023-12-01 13:25:50","%T");

　　常见的mysql日期格式见：链接

 　　DATEDIFF（date1,date2）表示日期“date1”与日期“date2”之间差几天。

SELECT DATEDIFF("2021-02-01","2020-12-01");

　　总结