1. 实践案例
在查询mysql数据库获取数据时，有这样一个需求：按每30分钟分组获取电量数据，形成1天48个数据点。
方法一：
select hour(a.CreateTime) 时点,
case when MINUTE(a.CreateTime)<30 then 1 else 2 end 半小时,sum(a.ChargeCapacity) 电量 from charging_order a
where DATE_FORMAT(a.CreateTime,'%Y-%m-%d')='2023-11-06'
group by hour(a.CreateTime),(case when MINUTE(a.CreateTime)<30 then 1 else 2 end)
结果如下：
方法二：
select CONCAT(DATE_FORMAT(a.CreateTime,'%H:'), case when MINUTE(a.CreateTime)<30 then '00' else '30' end) CreateTime,
sum(a.ChargeCapacity) Capacity from charging_order a
where DATE_FORMAT(a.CreateTime,'%Y-%m-%d')='2023-11-06'
group by CONCAT(DATE_FORMAT(a.CreateTime,'%H:'), case when MINUTE(a.CreateTime)<30 then '00' else '30' end)
order by CreateTime
方法三，最终方案（并增加，取近三天的各个时段的均值）。
select CONCAT('2023-11-06 ', CreateTime) RecordTime,format(sum(Capacity)/3,1) Capacity from
(select CONCAT(DATE_FORMAT(a.CreateTime,'%H:'), case when MINUTE(a.CreateTime)<30 then '00' else '30' end) CreateTime,
sum(a.ChargeCapacity) Capacity from charging_order a
where DATE_FORMAT(a.CreateTime,'%Y-%m-%d')<'2023-11-06'
and a.CreateTime>=date_sub(str_to_date('2023-11-06 00:00', '%Y-%m-%d %H:%i'), interval 3 day)
group by CONCAT(DATE_FORMAT(a.CreateTime,'%H:'), case when MINUTE(a.CreateTime)<30 then '00' else '30' end)
order by CreateTime ) bb
group by RecordTime
2. Mysql时间等处理技术
2.1. 日期时间的加减计算
date_add()
- 说明：date_add()：为当前日期增加一个时间间隔
- 用法：此函数可用于计算距离当前日期一个月之后的日期
- 语法格式：DATE_ADD(date,interval expr type)
- date：指定的时间日期
- interval：固定写法
- expr：所增加的时间间隔
- type：时间间隔的单位，包括：秒、分钟、小时、天、星期、月、季、年等
举例：
# 加1天
select now(),date_add(now(), interval 1 day);
# 加1小时
select now(),date_add(now(), interval 1 hour);
# 加1分钟
select now(),date_add(now(), interval 1 minute );
# 加1秒
select now(),date_add(now(), interval 1 second );
# 加1周
select now(),date_add(now(), interval 1 week);
# 加1个月
select now(),date_add(now(), interval 1 month);
# 加1季度
select now(),date_add(now(), interval 1 quarter );
# 加1年
select now(),date_add(now(), interval 1 year );
date_sub()
- 说明：date_sub()：为当前日期减去一个时间间隔
- 用法：此函数可用于计算距离当前日期一个月之前的日期
- 语法格式：DATE_SUB(date,interval expr type)
与date_add类似，不再详细举例。
select date_sub(str_to_date('2023-11-06 00:00', '%Y-%m-%d %H:%i'), interval 3 day)
2.2. 字符串转时间
STR_TO_DATE()函数的语法：
STR_TO_DATE(str,fmt);
STR_TO_DATE()根据fmt格式字符串将str字符串转换为日期值。 STR_TO_DATE()函数可能会根据输入和格式字符串返回DATE，TIME或DATETIME值。 如果输入字符串是非法的，则STR_TO_DATE()函数返回NULL。
STR_TO_DATE()函数扫描输入字符串来匹配格式字符串。格式字符串可能包含以百分比(%)字符开头的文字字符和格式说明符。 查看格式说明符列表的DATE_FORMAT函数。
SELECT STR_TO_DATE('2023-11-06 00:00:00', '%Y-%m-%d %H:%i:%s')
2.3. 字符串合并函数
CONCAT() 函数用于将多个字符串连接成一个字符串。语法及使用特点：
CONCAT(str1,str2,…)
返回结果为连接参数产生的字符串。如有任何一个参数为NULL ，则返回值为 NULL。可以有一个 或多个参数。
2.4. CASE … WHEN
case when 的语法有两种：
# 简单CASE函数法
CASE 要判断的字段或表达式
WHEN 常量1 THEN 要显示的值1或语句1（如果是语句，结尾需要加上分号;）
[WHEN 常量2 THEN 要显示的值2或语句2]
[…]
[ELSE 要显示的值n或语句n]
END
# CASE搜索函数法
CASE
WHEN 条件1 THEN 要显示的值1或语句1（如果是语句，结尾需要加上分号;）
[WHEN 条件2 THEN 要显示的值2或语句2]
[…]
[ELSE 要显示的值n或语句n]
END
比较“简单CASE函数法”和“CASE搜索函数法”：
- “简单CASE函数法”：语法更简洁，但功能不灵活好用，因为它只能对比单值的等式问题；
- “CASE搜索函数法”：语法有些繁琐，但功能灵活好用，既可以完成等式表达，也可以实现不等式表达。
2.5. HOUR与MINUTE
MINUTE(time)
返回一个整数，指定给定时间或日期时间值的分钟数，也就是返回time的分钟数(范围是0到59)。
select MINUTE('2023-11-06 10:05:03')
返回结果是5.
HOUR(time)
返回time的小时数(范围是0到23)。
2.6. 时间格式
DATE_FORMAT函数简介
要将日期值格式化为特定格式，请使用DATE_FORMAT函数。 DATE_FORMAT函数的语法如下：
DATE_FORMAT(date,format);
- date：是要格式化的有效日期值
- format：是由预定义的说明符组成的格式字符串，每个说明符前面都有一个百分比字符(%)。有关预定义说明符的列表，请参见下表。
DATE_FORMAT函数可以使用的参数格式
| 格式 | 描述 |
|---|---|
| %a | 缩写星期名(Sun…Sat) |
| %b | 缩写月份名(Jan…Dec) |
| %c | 月份(1…12) |
| %d | 月份中的天数，数字(00…31) |
| %e | 月份中的天数，数字(0…31) |
| %H | 小时(00…23) |
| %h | 小时(01…12) |
| %i | 分钟，数字(00…59) |
| %j | 一年中的天数(001…366) |
| %k | 小时(0…23) |
| %l | 小时(1…12) |
| %M | 月名字(January…December) |
| %m | 月，数字(00…12) |
| %p | AM或PM |
| %r | 时间，12小时(hh:mm:ss AM 或 PM) |
| %s | 秒(00…59) |
| %T | 时间，24小时(hh:mm:ss) |
| %U | 一年中的周数(00…53),星期日是一周的第一天 |
| %u | 一年中的周数(00…53),星期一是一周的第一天 |
| %Y | 年份，数字，4位 |
| %y | 年份，数字，2位 |
例如：
select DATE_FORMAT(now(),'%Y-%m-%d %H:%i:%s')
2023-11-11 18:07:19
3. pandas使用结果集出现的问题
返回结果集出现#,###.00式样的数据，例如：“1，024.1”。pandas解决方案是滤除逗号。
charging_order['Capacity'] = charging_order['Capacity'].str.replace(',', '').astype('float')
参考：
测试界的飘柔. MySQL数据库时间计算的用法. CSDN博客. 2023.07
山茶花开时。 . [Mysql] DATE_FORMAT函数. CSDN博客. 2023.05
