MySQL必知必会09：时间函数-时间类数据处理

最新推荐文章于 2023-05-08 20:03:27 发布
最新推荐文章于 2023-05-08 20:03:27 发布
阅读量3.4k 收藏
点赞数
分类专栏： MySQL 文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_31362767/article/details/123602521
版权

阅读整理自《MySQL 必知必会》- 朱晓峰，详细内容请登录 极客时间 官网购买专栏。

文章目录


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

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

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

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

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

销售单头表（demo.transactionhead)

transactionidtransactionnocashieridmemberidoperatoridtransdate
101201912010000011112019-12-01 09:25:56
201201912020000011NULL22019-12-02 09:50:50
301201912020000021NULL12019-12-02 10:10:05
401202011020000011212020-11-02 11:15:06
501202011020000021122020-11-02 12:20:03
601202011020000031NULL12020-11-02 13:05:01
701202012020000011212020-12-02 15:25:22
801202012020000021NULL12020-12-02 17:10:25
901202012020000031NULL12020-12-02 18:35:33
1001202012020000041NULL12020-12-02 19:33:55

销售明细表（demo.transactiondetails)

transactioniditemnumberquantitypricesalesvalue
11289178
125525
21389267
226530
3118989
3210550
4310330
52405200
61589445
726530
83133
91289178
102236

商品信息表（demo.goodsmaster）

itemnumberbarcodegoodsnamespecificationunitsalesprice
11书16开本89
22笔10支装包5
33橡皮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)

查询的过程是这样的：

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

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

extract() 函数中的 hour 表示要获取时间的类型，而 hour() 是一个函数，hour(time) 可以单独使用，表示返回 time 的小时部分信息。

select hour(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 hour(b.transdate)
order by hour(b.transdate);

其他时间函数：

  • 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 月的销售数据。

步骤：

  1. 用 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                                |
+-------------------------------------------+

  2. 获取 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                                                              |
+-------------------------------------------------------------------------+

  3. 获取 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 作为统计销售额的起始日期，可能就多算了这一天的销售，所以应该找这个时间的下一天。

  4. 计算 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(week)：获取日期“date”是周几。1 表示周日，2 表示周一，以此类推，直到 7 表示周六。

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

这个表中的信息表示单品打折的时间和折扣率：编号是 1 的商品，周一、周三和周五打折，折扣率分别是 9 折、75 折和 88 折；编号是 2 的商品，周二、周四和周六打折，折扣率分别是 5 折、65 折和 8 折。周日，所有商品打 5 折。

branchiditemnumberweekdaydiscountrate
1110.9
1130.75
1150.88
1220.5
1240.65
1260.8
1170.5
1270.5
1370.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 |
+------------+------+----------+--------+--------+----------+

MySQL 中 CASE 函数的语法如下：

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                              |
+------------------------------------------+

时间格式：https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-format

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

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

小结

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

其他

构建外键的时候，出现一个报错，1170 - BLOB/TEXT column ‘xxx’ used in key specification without a key lenght。

请添加图片描述

排查发现是 demo.discountrule 表的字段类型搞错了：

mysql> describe demo.discountrule;
+--------------+------+------+-----+---------+-------+
| Field        | Type | Null | Key | Default | Extra |
+--------------+------+------+-----+---------+-------+
| branchid     | int  | YES  |     | NULL    |       |
| itemnumber   | text | YES  |     | NULL    |       |
| weekday      | text | YES  |     | NULL    |       |
| discountrate | text | YES  |     | NULL    |       |
+--------------+------+------+-----+---------+-------+

mysql> alter table demo.discountrule change itemnumber itemnumber int;  -- 修改表字段类型

mysql> describe demo.discountrule;
+--------------+------+------+-----+---------+-------+
| Field        | Type | Null | Key | Default | Extra |
+--------------+------+------+-----+---------+-------+
| branchid     | int  | YES  |     | NULL    |       |
| itemnumber   | int  | YES  | MUL | NULL    |       |
| weekday      | text | YES  |     | NULL    |       |
| discountrate | text | YES  |     | NULL    |       |
+--------------+------+------+-----+---------+-------+
<花开花落>
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
mysql只截取到小时_MySQL如何获取一个指定时间中的小时数(Hour函数)呢？
weixin_35891825的博客
01-18 8655
摘要:下文讲述MySQL数据库中获取指定时间数据中的小时数的方法分享，如下所示；实现思路:使用hour(time)系统函数即可返回日期中的年份信息hour系统函数语法说明:hour(time)------参数说明----time:时间数据------返回值说明-----返回时间数据中的小时部分注意事项:1.输入一个异常时间数据，会返回一个NULL2.输入一个日期时间数据，会返回一个异常数据及警告信...
MySQL学习笔记（长期更新）
FirstMrRight的博客
05-24 273
002-字段：这么多字段类型，该怎么定义 字段评估：需要考虑存储空间和可靠性的平衡 类型选择：确保数据不会超过取值范围 注意： 精度问题： 浮点数存在精度丢失的问题，对精度要求高的字段（如价格）不要使用，原因是MySQL采用二进制存储数据，如果尾数不是0或5，就需要使用四舍五入的方式来表达，定点数类型：DECIMAL(将十进制数的整数部分和小数部分拆开，分别转换成十六进制数存储)。 主键问题 text类型长度不定，所以无法做主键，可使用char、varchar类型 时间类型 项目中使用时间类型优先考虑使用
MySQL常用的日期时间函数
热门推荐
m0_70157790的博客
05-12 5万+
目录 1.日期(date)函数 2.时间(time)函数 3.日期和时间函数 4.日期和时间比较 5.日期和时间运算 6.日期和时间的格式化 1.日期(date)函数 返回当前日期：CURDATE(), CURRENT_DATE(), CURRENT_DATE 2.时间(time)函数 返回当前时间：CURTIME(), CURRENT_TIME(), CURRENT_TIME 3.日期和时间函数 返回当前日期和时间：CURRENT_TIMESTAMP...
Mysql 获取当前日期前一天（DATE_SUB()函数和DATE_ADD()的用法）
Welcome You(*^_^*)
08-23 2万+
Mysql 获取当前日期前一天 DATE_SUB（）函数的用法 项目需求备注： 项目需求 这是关于一个报名活动的需求，因为报名开始时间和活动结束时间有时间间隔的，小程序展示需要，所以就展开下文 获取当前日期:select curdate() 获取当前时间的前两天:select date_sub(curdate(),interval 2 day) **获取当前日期后一天:select date_sub(curdate(),interval -1 day) ** 举例： <!-- flag
MySQL获取当前日期、时间、时间戳函数
最新发布
wangshiqi666的博客
05-08 2万+
mysql时间函数
在 MySQL 中处理日期和时间 - 第二章节
Navicat 官方账号
04-17 770
欢迎回到这个关于在 MySQL 中处理日期和时间的系列。在前两部分中，我们探讨 MySQL 的时态数据类型。第 1 部分介绍了 DATE、TIME 和 DATETIME 数据类型，而本部分将介绍余下的 TIMESTAMP 和 YEAR 类型。
Mysql必知必会表数据.ZIP
10-21
"Mysql必知必会表数据"这个主题涵盖了数据库设计、SQL查询语言、事务处理、索引优化以及数据库性能提升等多个方面。以下是关于这些关键知识点的详细阐述： 1. **数据库设计**：在MySQL中，设计良好的表数据结构是至...
MySQL-Crash-Course：:reminder_ribbon:《 MySQL必知必会》:salt:Basic-语法〜
02-15
《MySQL必知必会》是一本非常实用的教程书籍，旨在帮助初学者快速掌握MySQL的基础语法和操作。在这个MySQL-Crash-Course中，我们将深入探讨MySQL的基本语法，帮助你扎实地建立数据库管理技能。 1. **SQL语言基础**...
MySQL中的时间类型
Valineliu
08-31 2万+
时间是一类重要的数据，MySQL中有多种关于时间的类型可以选择。这篇文章主要介绍MySQL中的时间类型，主要参考MySQL文档：https://dev.mysql.com/doc/refman/8.0/en/date-and-time-types.html。
时间函数与公式节点（公式节点）
qq_40631424的博客
07-09 292
一文速学-玩转MySQL获取时间、格式转换各类操作方法详解
master_hunter的博客
06-17 3484
时间在数据库中经常作为时间索引，在数据入库和出库以及更新的时候都需要变化。在一些指标计算或者是提取某段时间的数据时，都会根据数据库中的时间索引数据进行操作。因此很大一部分我们操作数据都得先从时间数据下手，但是想要真正提取到我们想要的时间作为索引，还需要我们掌握许多功能函数方便我们操作，这是一个比较复杂的运用过程。因此特地写下这篇文章，记录一些十分好用常用的处理数据库SQL时间数据的函数，以及实例运用这些函数完成一些复杂查询任务。希望能够帮助到正在看此博文的各位，如果还有什么问题解决不了尽请在评论区提出，博主
mysql日期转星期
繁星呀
04-21 3313
mysql自带函数dayofweek，和中国的星期对不上 周一到周六：2~7 周七：1 select if(dayofweek(curdate()) = 1,7,dayofweek(curdate()) -1);
mysql 以周为单位记录数据_mysql处理N个时间单位(N天、N周、N月、N年)以前或以后的数据的方法...
weixin_39785669的博客
12-22 443
1.星期操作DAYOFWEEK(date)返回日期date是星期几(1=星期天,2=星期一,……7=星期六)mysql> select DAYOFWEEK('1998-02-03');-> 3WEEKDAY(date)返回日期date是星期几(0=星期一,1=星期二,……6= 星期天)mysql> select WEEKDAY('1997-11-05');-> 2DAYNA...
mysql获取时间的函数是_MySQL获取时间函数
weixin_39928099的博客
01-18 189
1、 获得当前日期+时间(date + time)函数：now()mysql> SELECT now();+---------------------+| now() |+---------------------+| 2017-06-15 09:16:56 |+---------------------+1 row in set (0.00 sec)2、 获得当...
MySQL 获取时间函数
佳佳乐的博客
06-05 1万+
1. 选取日期时间的各个部分：日期、时间、年、季度、月、日、小时、分钟、秒、微秒（注意写法  大小写和在SQL语句内自动加引号问题引号要去除） 1. 选取日期时间的各个部分：日期、时间、年、季度、月、日、小时、分钟、秒、微秒 set @dt = '2008-09-10 07:15:30.123456'; select date(@dt); -- 2008-09-10 select time(@...
MySQL获取时间函数
晚晴小筑
06-15 1064
1、 获得当前日期+时间（date + time）函数：now() mysql> SELECT now(); +---------------------+ | now() | +---------------------+ | 2017-06-15 09:16:56 | +---------------------+ 1 row in set (0.00 sec)
MySQL日期类型的处理总结
Morty的技术乐园
05-31 4300
一、概述 MySQL中的日期类型包括以下5种： 类型 大小 (字节) 范围 格式 用途 DATE 3 1000-01-01/9999-12-31 YYYY-MM-DD 日期值 TIME 3 '-838:59:59'/'838:59:59' HH:MM:SS 时间值或持续时间 YEAR 1 19...
Mysql 字段类型转化 和 时间类型相关处理
HarryChenj的专栏
05-15 3209
I） 字段类型转化
MySQL 必知必会11-20.pdf
10-04
MySQL必知必会11-20章是数据库管理与分析领域的核心内容，涵盖了数据处理、汇总、分组、子查询、表联结以及高级操作等方面。本章节的主要知识点包括： 1. 数据处理函数 (Chapter 11) - MySQL提供了丰富的文本处理...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值