MySQL 查询固定时间段的数据

今天想用MySQL查点东西，需求如下：

查一周时间内的每天上午9点到11点半和下午1点到3点的所有数据。

数据库表结构比较简单，就是Zabbix的默认数据库表结构，查一下历史数据表。

history_uint 表结构如下：

字段名	类型	备注
itemid	bigint	监控项id
clock	int	epoch时间戳（unix时间戳），精确到秒
value	bigint	监控项的值
ns	int	纳秒

 

 

 

 

 

 

直接贴SQL吧，贴完再说思路：

SELECT
	a.itemid, FROM_UNIXTIME(a.clock) a_time, a.value
FROM
	history_uint a
WHERE
	itemid=29176
AND
	-- 时间段限制，一周或者任意时间段
	-- DATE_SUB(now(),INTERVAL 7 DAY) <= FROM_UNIXTIME(a.clock)
	a.clock BETWEEN UNIX_TIMESTAMP('2019-03-24 09:00:00') AND UNIX_TIMESTAMP('2019-03-26 15:00:00')
AND
	-- 每天时间段限制，格式化日期到时分秒
(
	DATE_FORMAT(FROM_UNIXTIME(a.clock), '%H:%i:%S') BETWEEN '09:00:00' AND '11:30:00'
	OR
	DATE_FORMAT(FROM_UNIXTIME(a.clock), '%H:%i:%S') BETWEEN '13:00:00' AND '15:00:00'
)

实际上就是这个时间段格式不太会写，导致这个where子句一直写不出来。先将unix时间戳转成日期格式，再将日期格式格式化成时分秒，忽略日期，这样就可以进行时分秒上的时间判断了，注意由于是两个时间段，因此是或的关系，并且最后的时间段判断要括号括起来。

主要涉及MySQL时间转换这几个函数：

1.unix时间戳转日期格式 FROM_UNIXTIME(a.clock,"format")

例子1：SELECT FROM_UNIXTIME(1553070856)

默认不跟格式，如下结果：

例子2：SELECT FROM_UNIXTIME(1553070856, "%Y-%m-%d %h:%i:%S")

2.日期格式转unix时间戳 UNIX_TIMESTAMP('time')

例子：UNIX_TIMESTAMP('2019-03-24 09:00:00')

3.时间日期格式化 DATE_FORMAT(time,'format')

例子：SELECT DATE_FORMAT(FROM_UNIXTIME(1553070856), '%H:%i:%S')

4.日期格式化参数

"format"参数：

常用例子："%Y-%m-%d %H:%i:%S"   2019-03-20 16:34:16

%Y	年,四位格式：2019
%y	年，两位格式：19
%M	月，英文格式：March
%m	月，两位格式：03
%D	日，带英文后缀：20th
%d	日，两位格式：20
%H	时，24小时格式：16
%h	时，12小时格式：04
%i	分，两位格式：34
%S	秒，两位格式：16，如果是8秒，则显示08
%s	秒，普通格式：如果是8秒，则显示8