MySQL必知必会 09丨时间函数：时间类数据，MySQL是怎么处理的？

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

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

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

超市的经营者提出，他们希望通过实际的销售数据，了解到一天当中什么时间段卖得好，什么时间段卖得不好，这样他们就可以根据不同时间的销售情况，合理安排商品陈列和人员促销，以实现收益最大化。要达到这个目标，我们就需要统计一天中每小时的销售数量和销售金额。

这里涉及 3 组数据，分别是销售单头表（demo.transactionhead)、销售单明细表 (demo.transactiondetails) 和商品信息表（demo.goodsmaster）（为了便于你理解，表的结构和表里的记录都是经过简化的）。

销售单头表（demo.transactionhead)，包含了销售单的整体信息，包括流水单号、交易时间、收款机编号、会员编号和收银员编号等。

transactionid	transactionno	cashierid	memberid	operatorid	transdate
1	0120191201000001	1	1	1	2019-12-01 09:25:56
2	0120191202000001	1	NULL	2	2019-12-02 09:50:50
3	0120191202000002	1	NULL	1	2019-12-02 10:10:05
4	0120201102000001	1	2	1	2020-11-02 11:15:06
5	0120201102000002	1	1	2	2020-11-02 12:20:03
6	0120201102000003	1	NULL	1	2020-11-02 13:05:01
7	0120201202000001	1	2	1	2020-12-02 15:25:22
8	0120201202000002	1	NULL	1	2020-12-02 17:10:25
9	0120201202000003	1	NULL	1	2020-12-02 18:35:33
10	0120201202000004	1	NULL	1	2020-12-02 19:33:55

销售明细表（demo.transactiondetails)保存的是交易明细数据，包括商品编号、销售数量、价格、销售金额等。

transactionid	itemnumber	quantity	price	salesvalue
1	1	2	89	178
1	2	5	5	25
2	1	3	89	267
2	2	6	5	30
3	1	1	89	89
3	2	10	5	50
4	3	10	3	30
5	2	40	5	200
6	1	5	89	445
7	2	6	5	30
8	3	1	3	3
9	1	2	89	178
10	2	2	3	6

商品信息表（demo.goodsmaster）主要包括商品编号、条码、商品名称、规格、单位和售价。

itemnumber	barcode	goodsname	specification	unit	salesprice
1	1	书	16开	本	89
2	2	笔	10支装	包	5
3	3	橡皮	NULL	个	3

销售单明细表通过流水编号与销售单头表关联，其中流水编号是外键。通过流水编号，销售单明细表引用销售单头表里的交易时间、会员编号等信息，同时，通过商品编号与商品信息表关联，引用商品信息表里的商品名称等信息。如图所示：

需求：统计一天中每小时的销售数量和销售金额

要统计一天中每小时的销售情况，实际上就是要把销售数据按照小时进行分组统计。那么，解决问题的关键，就是把交易时间的小时部分提取出来。这就要用到 MySQL 的日期时间处理函数 extract() 和 hour() 了。

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

mysql> select extract(hour from b.transdate) as time_part,
    -> sum(a.quantity) as quantity,
    -> sum(a.salesvalue) as salesvalue
    -> from demo.transactiondetails as a
    -> join demo.transactionhead as b on (b.transactionid = a.transactionid)
    -> group by extract(hour from b.transdate)
    -> order by extract(hour from b.transdate);
+-----------+----------+------------+
| time_part | quantity | salesvalue |
+-----------+----------+------------+
|         9 |   16.000 |     500.00 |
|        10 |   11.000 |     139.00 |
|        11 |   10.000 |      30.00 |
|        12 |   40.000 |     200.00 |
|        13 |    5.000 |     445.00 |
|        15 |    6.000 |      30.00 |
|        17 |    1.000 |       3.00 |
|        18 |    2.000 |     178.00 |
|        19 |    2.000 |       6.00 |
+-----------+----------+------------+
9 rows in set (0.00 sec)

查询的过程是这样的：

从交易时间中抽取小时信息：extract(hour from b.transdate)
按交易的小时信息分组；
按分组统计销售数量和销售金额的和；
按交易的小时信息排序。

需要获取其他时间部分的信息，可以参考下时间单位。

extract() 函数中的 hour 表示要获取时间的类型，而 hour() 是一个函数，hour(time) 可以单独使用，表示返回 time 的小时部分信息。可以把 EXTRACT 函数改成 HOUR 函数，来实现相同的功能，如下所示：

mysql> 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函数

其他时间函数：

YEAR（date）：获取 date 中的年
MONTH（date）：获取 date 中的月
DAY（date）：获取 date 中的日
HOUR（date）：获取 date 中的小时
MINUTE（date）：获取 date 中的分
SECOND（date）：获取 date 中的秒

计算日期时间的函数

date_add（date, interval 表达式 type）：表示计算从时间点“date”开始，向前或者向后一段时间间隔的时间。“表达式”的值为时间间隔数，正数表示向后，负数表示向前，“type”表示时间间隔的单位（比如年、月、日等）
last_day（date）：表示获取日期时间“date”所在月份的最后一天的日期。
需求：计算这个月单品销售金额的统计，以及与去年同期相比的增长率。

假设今天是 2020 年 12 月 10 日，那么也就是要需要获取 2019 年 12 月的销售数据。

步骤：

用date_add函数，获取到 2020 年 12 月 10 日上一年的日期：2019 年 12 月 10 日

mysql> select date_add('2020-12-10', interval - 1 year);
+-------------------------------------------+
| date_add('2020-12-10', interval - 1 year) |
+-------------------------------------------+
| 2019-12-10                                |
+-------------------------------------------+

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

mysql> select date_add(date_add('2020-12-10', interval - 1 year), interval - 1 month);
+-------------------------------------------------------------------------+
| date_add(date_add('2020-12-10', interval - 1 year), interval - 1 month) |
+-------------------------------------------------------------------------+
| 2019-11-10                                                              |
+-------------------------------------------------------------------------+

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

mysql> select last_day(date_add(date_add('2020-12-10', interval - 1 year), interval - 1 month));
+-----------------------------------------------------------------------------------+
| last_day(date_add(date_add('2020-12-10', interval - 1 year), interval - 1 month)) |
+-----------------------------------------------------------------------------------+
| 2019-11-30                                                                        |
+-----------------------------------------------------------------------------------+

如果用 11-30 作为统计销售额的起始日期，可能就多算了这一天的销售，所以应该找这个时间的下一天。

计算 2019 年 11 月 30 日后一天的日期

mysql> select date_add(last_day(date_add(date_add('2020-12-10', interval - 1 year), interval - 1 month)), interval +1 day);
+--------------------------------------------------------------------------------------------------------------+
| date_add(last_day(date_add(date_add('2020-12-10', interval - 1 year), interval - 1 month)), interval +1 day) |
+--------------------------------------------------------------------------------------------------------------+
| 2019-12-01                                                                                                   |
+--------------------------------------------------------------------------------------------------------------+

此时，终于获得了正确的起始日期：2019 年 12 月 01 日。

同样类似方法，获得截止日期：

mysql> select date_add(last_day(date_add('2020-12-10', interval - 1 year)), interval +1 day);
+--------------------------------------------------------------------------------+
| date_add(last_day(date_add('2020-12-10', interval - 1 year)), interval +1 day) |
+--------------------------------------------------------------------------------+
| 2020-01-01                                                                     |
+--------------------------------------------------------------------------------+

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

除了 date_add()，adddate()、date_sub() 和subdate()也能达到同样的效果：

adddate()：跟 date_add() 用法一致

mysql> select adddate(last_day(adddate('2020-12-10', interval - 1 year)), interval +1 day);
+------------------------------------------------------------------------------+
| adddate(last_day(adddate('2020-12-10', interval - 1 year)), interval +1 day) |
+------------------------------------------------------------------------------+
| 2020-01-01                                                                   |
+------------------------------------------------------------------------------+

date_sub()，subdate()：与 date_add() 用法类似，方向相反，执行日期的减操作

mysql> select date_sub('2020-12-10', interval 1 year);
+-----------------------------------------+
| date_sub('2020-12-10', interval 1 year) |
+-----------------------------------------+
| 2019-12-10                              |
+-----------------------------------------+

mysql> select subdate('2020-12-10', interval -1 year);
+-----------------------------------------+
| subdate('2020-12-10', interval -1 year) |
+-----------------------------------------+
| 2021-12-10                              |
+-----------------------------------------+

其他日期时间函数

curdate()、dayofweek()、date_format 、datediff()

curdate()：获取当前的日期。日期格式为“YYYY-MM-DD”，也就是年月日的格式。
dayofweek(date)：获取日期“date”是周几。1 表示周日，2 表示周一，以此类推，直到 7 表示周六。

举例：按照周中不同的日期进行促销，单品促销信息（demo.discountrule）。

这个表中的信息表示单品打折的时间和折扣率：

编号是 1 的商品，周一、周三和周五打折，折扣率分别是 9 折、75 折和 88 折；
编号是 2 的商品，周二、周四和周六打折，折扣率分别是 5 折、65 折和 8 折。
周日，所有商品打 5 折。

branchid	itemnumber	weekday	discountrate
1	1	1	0.9
1	1	3	0.75
1	1	5	0.88
1	2	2	0.5
1	2	4	0.65
1	2	6	0.8
1	1	7	0.5
1	2	7	0.5
1	3	7	0.5

create table demo.discountrule 
(
branchid int,
itemnumber text,
weekday text,
discountrate text
);
select * from demo.discountrule;

查询今天商品的全部折后价格：

mysql> select curdate() as 日期,
    -> case dayofweek(curdate()) - 1 when 0 then 7 else dayofweek(curdate()) - 1 end as 周几,
    -> a.goodsname as 商品名称,
    -> a.salesprice as 价格,
    -> ifnull(b.discountrate, 1) as 折扣率,
    -> a.salesprice * ifnull(b.discountrate, 1) as 折后价格
    -> from demo.goodsmaster a
    -> left join demo.discountrule b on (a.itemnumber = b.itemnumber and case dayofweek(curdate()) - 1 when 0 then 7 else dayofweek(curdate()) -1 end = b.weekday);
+------------+------+----------+--------+--------+----------+
| 日期       | 周几 | 商品名称 | 价格   | 折扣率 | 折后价格 |
+------------+------+----------+--------+--------+----------+
| 2022-03-09 |    3 | 书       | 89.000 | 0.75   |    66.75 |
| 2022-03-09 |    3 | 笔       |  5.000 | 1      |        5 |
| 2022-03-09 |    3 | 橡皮     |  3.000 | 1      |        3 |
+------------+------+----------+--------+--------+----------+

这个查询，用到了 CURDATE（）函数来获取当前日期，也用到了 DAYOFWEEK（）函数来获取当前是周几的信息。由于 DAYOFWEEK() 函数，以周日为 1 开始计，周一是2……，周六是 7，而数据表中是从周一为 1 开始计算，为了对齐，我用到了条件判断函数CASE，我来解释下这个函数。

MySQL 中 CASE 函数的语法如下：

1	CASE 表达式 WHEN 值1 THEN 表达式1 [ WHEN 值2 THEN 表达式2] ELSE 表达式m END

在当前业务查询中，“表达式”有 7 种可能的值。通过 case 函数，可以根据 dayofweek() 函数返回的值对每个返回值进行处理，从而跟促销信息表中的字段 weekday 对应。

关系图：

除了获取特定的日期，还经常需要把日期按照一定的格式显示出来，这就要用到日期时间格式化的函数 date_format()，它表示将日期时间“date”按照指定格式显示。

mysql> select date_format("2020-12-01 13:25:50", "%T");  -- 24小时制
+------------------------------------------+
| date_format("2020-12-01 13:25:50", "%T") |
+------------------------------------------+
| 13:25:50                                 |
+------------------------------------------+

mysql> select date_format("2020-12-01 13:25:50", "%r");  -- 上下午时间
+------------------------------------------+
| date_format("2020-12-01 13:25:50", "%r") |
+------------------------------------------+
| 01:25:50 PM                              |
+------------------------------------------+

datediff(date1,date2)，表示日期“date1”与日期“date2”之间差几天

mysql> select datediff("2021-02-01","2020-12-01");
+-------------------------------------+
| datediff("2021-02-01","2020-12-01") |
+-------------------------------------+
|                                  62 |
+------------------------------------+

小结

今天，我们学习了 MySQL 的时间处理函数，包括获取日期时间类型数据中部分信息的函数、计算日期时间的函数和获取特定日期的函数，我用图片来帮你汇总了下。

获取日期时间数据中部分信息函数
- extract() 获取日期时间中指定的值
- hour() 小时；
- year() 年份；
- month() 月份
- day() 日；
- minute() 分；
- second() 秒
日期时间计算函数
- date_add() 、adddate()计算从某个时间点出发过去或未来一段时间间隔的时。
- subdate()、date_sub() 与date_add()相似，方向相反。
- datediff() 计算两个时间点之间相隔的天数
- last_day() 计算给定时间点当月的最后一天的日期
其他时间函数
- curdate() 当前时间
- weekofday() 今天是星期几，周日为1，周一为2，递推
- date_format() 格式化日期时间

MySQL 中获取的时间，其实就是 MySQL 服务器计算机的系统时间。如果你的系统有一定规模，需要在多台计算机上运行，就要注意时间校准的问题。

比如我们的信息系统受门店经营环境和操作人员的素质所限，有时会遇到误操作、停电等故障而导致的计算机系统时间失准问题。这对整个信息系统的可靠性影响非常大。

针对这个问题，有 2 种解决办法。

第一种方法是，可以利用 Windows 系统自带的网络同步的方式，来校准系统时间。
另一种办法就是，门店统一从总部 MySQL 服务器获取时间。

由于总部的服务器的配置和运维状况一般要好于门店，所以系统时间出现误差的可能性也较小。如果采用云服务器，系统时间的可靠性会更高。

transactionid	itemnumber	quantity	price	salesvalue
1	1	2	89	178
1	2	5	5	25
2	1	3	89	267
2	2	6	5	30
3	1	1	89	89
3	2	10	5	50
4	3	10	3	30
5	2	40	5	200
6	1	5	89	445
7	2	6	5	30
8	3	1	3	3
9	1	2	89	178
10	2	2	3	6

transactionid	itemnumber	quantity	price	salesvalue
1	1	2	89	178
1	2	5	5	25
2	1	3	89	267
2	2	6	5	30
3	1	1	89	89
3	2	10	5	50
4	3	10	3	30
5	2	40	5	200
6	1	5	89	445
7	2	6	5	30
8	3	1	3	3
9	1	2	89	178
10	2	2	3	6

Channing's Blog

MySQL必知必会 09丨时间函数：时间类数据，MySQL是怎么处理的？

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

计算日期时间的函数

其他日期时间函数

MySQL 中 CASE 函数的语法如下：

小结

transactionid	itemnumber	quantity	price	salesvalue
1	1	2	89	178
1	2	5	5	25
2	1	3	89	267
2	2	6	5	30
3	1	1	89	89
3	2	10	5	50
4	3	10	3	30
5	2	40	5	200
6	1	5	89	445
7	2	6	5	30
8	3	1	3	3
9	1	2	89	178
10	2	2	3	6