一 、 查某个时间段的数据
早高峰时间段查询(早上7点到九点)
SELECT * FROM table WHERE hour(时间字段) BETWEEN 07 and 09
但是遇到一个问题就是要查凌晨的数据就不行了
因为凌晨是当天到第二天 所以:
凌晨数据查询(晚上10点到次日凌晨6点)
SELECT * FROM table WHERE (hour(时间字段)>=22 or 6 >=hour(时间字段))
二、 查最接近的记录
查询场景:现在的需求是查询年龄最接近20岁的用户,获取前5个
我现在的数据库记录用户年龄的字段记录格式是"1995-05-20",字段名称birthday
解决思路:
1.首先查询时转换成用户年龄
日期格式转年龄的方法:
(1)当前年份 - 日期格式中的年份
date_format(now(),
'%Y'
) - from_unixtime(unix_timestamp(birthday),
'%Y'
)
(2)当前年份 - 日期格式中的年份(获取方法不同)
date_format(now(),
'%Y'
) -
year
(birthday)
2.通过order by asb()
排序获取最接近的数据
完整的语句:
SELECT
(date_format(now(), '%Y') - year(birthday)) as age
FROM user
WHERE
is_anchor = 1
ORDER BY abs(20 - age)
limit 0,5
三、查某个时间段的且最接近的记录数据
查询场景:现在的需求是查询某个用户在2020年中最早出单时间(凌晨5点-9点,且最接近5点的数据)和2020年中最晚出单时间(晚上10点-次日凌晨4点,取最靠近凌晨4点的出单时间)
我现在的数据库记录出单时间的字段记录格式是"yyyy-MM-dd HH:mm:ss",字段名称GMT_MODIFIED
解决思路:
最早出单时间:
1. 首先查询凌晨5-9点(小时 5-8)的数据, hour(p.GMT_MODIFIED) BETWEEN 05 and 08
2.再查最接近5点的数据 select hour(p.GMT_MODIFIED) as hourss from hip_sal_policy p where ORDER BY abs(5 - hourss)
最晚出单时间:
1. 首先查询晚上10点-次日凌晨4点(小时 22-4)的数据, hour(p.GMT_MODIFIED) >= 22 or 04 >= hour(p.GMT_MODIFIED)
2.再查最接近4点的数据 select hour(p.GMT_MODIFIED) as hourss from hip_sal_policy p where ORDER BY abs(4 - hourss)
完整sql
最早出单时间(凌晨5-9点)
select
DATE_FORMAT(p.GMT_MODIFIED,'%H:%i') policyTime,
hour(p.GMT_MODIFIED) as hourss
from hip_sal_policy p
where
p.status ='1' and p.creator = #{userId}
and p.GMT_MODIFIED BETWEEN '2020-01-01 00:00:00' and '2020-12-31 23:59:59'
and hour(p.GMT_MODIFIED) BETWEEN 05 and 08
ORDER BY abs(5 - hourss)
limit 1
最晚出单时间(晚上10点-次日凌晨4点)
select
DATE_FORMAT(p.GMT_MODIFIED,'%H:%i') policyTime,
hour(p.GMT_MODIFIED) as hourss
from hip_sal_policy p
where
p.status ='1' and p.creator = '675609'
and p.GMT_MODIFIED BETWEEN '2020-01-01 00:00:00' and '2020-12-31 23:59:59'
and (hour(p.GMT_MODIFIED) >= 22 or 04 >= hour(p.GMT_MODIFIED))
ORDER BY abs(4 - hourss)
limit 1