MySQL的DATE_FORMAT函数的使用

吾爱主题阅读：128 2024-04-02 07:59:50 评论：0

假设某宝为鼓励大家双12买买买，奖励双十一那天订单最多的两位用户：分别是用户1：“剁手皇帝陈哈哈” 和用户2：“触手怪刘大莉” 一人一万元；

需求1：让你通过MySQL订单表，统计一下双11那天，这两位每小时的购买订单成交数；你会怎么写这条SQL呢？

记得几年前我刚接触MySQl，年少轻狂，在得知不用考虑效率的情况下，我写了个接口循环二十四遍，发送24条SQL去查（捂脸），由于那个接口，被技术经理嘲讽~~表示他写的SQL比我吃的米都多。虽然我们山东人基本不吃米饭，但我还是羞愧不已。。

然后经理通过调用一个DATE_FORMAT函数分组查询处理一下，就ok了，效率是我的几十倍吧。从那时起，我暗自计划要深入研究SQL技巧。

第二天，由于和朋友开黑打了两把王者，计划延后了几年~

在MySQL中对于处理时间字段，有专门封装的DATE_FORMAT函数，可以说，DATE_FORMAT函数可以基本满足任何时间字段的处理需求。

DATE_FORMAT(date,format) 函数

参数解析：

1、date：代表具体时间字段，也可以为now()查询当前时间;
2、format：DATE_FORMAT将传来的Date类型数据转为自己需要的格式，如%Y-%m-%d %H:%i:%s会将传来的Time数据转为"yyyy-MM-dd HH:mm:ss"格式
%Y-%m-%d %H:%i:%s 与 yyyy-MM-dd HH:mm:ss 相对应，也是最常用的格式，这里举几个简单的栗子如下；

1 2 3 4 5 6 SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s' ); -- 结果：2020-12-07 22:18:58 SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i' ); -- 结果：2020-12-07 22:18 SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H' ); -- 结果：2020-12-07 22 SELECT DATE_FORMAT(NOW(), '%Y-%m-%d' ); -- 结果：2020-12-07 SELECT DATE_FORMAT(NOW(), '%H:%i:%s' ); -- 结果：22:18:58 SELECT DATE_FORMAT(NOW(), '%H' ); -- 结果：22

对于上面的需求1，用DATE_FORMAT函数的查询方式如下：

1 2 3 SELECT name as '用户名' ,DATE_FORMAT(createTime, '%Y-%m-%d %H' ) as '时间/小时' , count (*) as '订单量' from t_order where DATE_FORMAT(createTime, '%Y-%m-%d' ) = '2020-11-11' GROUP BY DATE_FORMAT(createTime, '%Y-%m-%d %H' );

查询结果：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 mysql> SELECT name as '用户名' ,DATE_FORMAT(createTime, '%Y-%m-%d %H' ) as '时间/小时' , count (*) as '订单量' from t_order where DATE_FORMAT(createTime, '%Y-%m-%d' ) = '2020-11-11' GROUP BY DATE_FORMAT(createTime, '%Y-%m-%d %H' ); + ------------------+---------------+-----------+ | 用户名 | 时间/小时 | 订单量 | + ------------------+---------------+-----------+ | 剁手皇帝陈哈哈 | 2020-11-11 00 | 0 | | 剁手皇帝陈哈哈 | 2020-11-11 01 | 10 | | 剁手皇帝陈哈哈 | 2020-11-11 02 | 6 | | 剁手皇帝陈哈哈 | 2020-11-11 03 | 3 | | 剁手皇帝陈哈哈 | 2020-11-11 04 | 0 | | 剁手皇帝陈哈哈 | 2020-11-11 05 | 0 | | 剁手皇帝陈哈哈 | 2020-11-11 06 | 0 | | 剁手皇帝陈哈哈 | 2020-11-11 07 | 0 | | 剁手皇帝陈哈哈 | 2020-11-11 08 | 0 | | 剁手皇帝陈哈哈 | 2020-11-11 09 | 0 | | 剁手皇帝陈哈哈 | 2020-11-11 10 | 0 | | 剁手皇帝陈哈哈 | 2020-11-11 11 | 0 | | 剁手皇帝陈哈哈 | 2020-11-11 12 | 12 | | 剁手皇帝陈哈哈 | 2020-11-11 13 | 6 | | 剁手皇帝陈哈哈 | 2020-11-11 14 | 3 | | 剁手皇帝陈哈哈 | 2020-11-11 15 | 7 | | 剁手皇帝陈哈哈 | 2020-11-11 16 | 2 | | 剁手皇帝陈哈哈 | 2020-11-11 17 | 3 | | 剁手皇帝陈哈哈 | 2020-11-11 18 | 11 | | 剁手皇帝陈哈哈 | 2020-11-11 19 | 2 | | 剁手皇帝陈哈哈 | 2020-11-11 20 | 1 | | 剁手皇帝陈哈哈 | 2020-11-11 21 | 3 | | 剁手皇帝陈哈哈 | 2020-11-11 22 | 2 | | 剁手皇帝陈哈哈 | 2020-11-11 23 | 0 | + ------------------+---------------+-----------+ 24 rows in set (0.00 sec)

这里的GROUP BY DATE_FORMAT(createTime,'%Y-%m-%d %H')代表的是“年月日 + 小时”合在一起为单位，具体到的是某天的小时；

当然，也可以写成以下两种形式，都是按小时来划分，但是…：

1、GROUP BY DATE_FORMAT(createTime,'%H')
2、GROUP BY HOUR(createTime)
需要注意的是，当where条件指定了某一天时，这三种的作用是相同的，但如果where条件没指定某一天，就会大不相同，我们来看看查询结果；

1 2	`SELECT` `name` `as` `'用户名'` `,DATE_FORMAT(createTime,` `'%H'` `)` `as` `'时间/小时'` `,` `count` `(*)` `as` `'订单量'` `from` `t_order` `GROUP` `BY` `DATE_FORMAT(createTime,` `'%H'` `);`

查询结果

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 mysql> SELECT name as '用户名' ,DATE_FORMAT(createTime, '%H' ) as '时间/小时' , count (*) as '订单量' from t_order GROUP BY DATE_FORMAT(createTime, '%H' ); + -----------------+---------------+-----------+ | 用户名 | 时间/小时 | 订单量 | + -----------------+---------------+-----------+ | 触手怪刘大莉 | 00 | 11 | | 触手怪刘大莉 | 01 | 302 | | 触手怪刘大莉 | 02 | 277 | | 触手怪刘大莉 | 03 | 122 | | 触手怪刘大莉 | 04 | 6 | | 触手怪刘大莉 | 05 | 11 | | 触手怪刘大莉 | 06 | 0 | | 触手怪刘大莉 | 07 | 0 | | 触手怪刘大莉 | 08 | 1 | | 触手怪刘大莉 | 09 | 4 | | 触手怪刘大莉 | 10 | 5 | | 触手怪刘大莉 | 11 | 92 | | 触手怪刘大莉 | 12 | 1937 | | 触手怪刘大莉 | 13 | 1602 | | 触手怪刘大莉 | 14 | 108 | | 触手怪刘大莉 | 15 | 78 | | 触手怪刘大莉 | 16 | 110 | | 触手怪刘大莉 | 17 | 108 | | 触手怪刘大莉 | 18 | 138 | | 触手怪刘大莉 | 19 | 66 | | 触手怪刘大莉 | 20 | 44 | | 触手怪刘大莉 | 21 | 59 | | 触手怪刘大莉 | 22 | 21 | | 触手怪刘大莉 | 23 | 8 | + -----------------+---------------+-----------+ 24 rows in set (0.01 sec)