问题描述
我想从表中选择最近的5分钟间隔.
I want to select from my table the last full 5 minute interval.
示例:
- 现在时间:上午09:32->选择-> 09:25-09:30 am
- 现在时间:10:44 pm->选择-> 10:35-10:40 pm
我知道如何将我的选择分为5分钟间隔
I know how to group my select into 5 minute intervals
date_sub(date_sub(starttime, INTERVAL (MINUTE(starttime) % 5) MINUTE), INTERVAL (SECOND(starttime)) SECOND) as INTERVAL_START,
date_add(date_sub(date_sub(starttime, INTERVAL (MINUTE(starttime) % 5) MINUTE), INTERVAL (SECOND(starttime)) SECOND), INTERVAL 5 MINUTE) as INTERVAL_END,
我也知道如何选择最近5分钟
I also know how to select the last 5 minutes
where (endtime between now()-Interval 5 minute and now())
但是如何获得上一个完整的5分钟间隔,如上面的示例所示?
But how do I get the last full 5 minute interval like shown in the example above?
推荐答案
您的问题仍然不太清楚,因为您没有提及基于输入的预期输出.但是,您可以使用此代码基于now()获取start_time和end_time.根据需要更改now()
.
Your question is still not very clear as you didn't mention expected output based on your input. However, you can use this code to get start_time and end_time based on now(). Change now()
as per your requirement.
select
date_sub(str_to_date(concat(hour(now()),':',floor(minute(now())/5)*5),'%H:%i'),interval 5 minute) as start_time,
str_to_date(concat(hour(now()),':',floor(minute(now())/5)*5),'%H:%i') as end_time,
now();
说明:首先将分钟除以5,然后将下限(除去小数点后)再乘以5.这将为您提供最接近的结束时间.从中减去5分钟即可获得start_time.
Explanation: First divide the minutes by 5, then take the floor(remove decimal) and multiply it by 5. This will give you nearest end time. Subtract 5 minutes from it to get start_time.
这篇关于MySQL:选择上一个完整的5分钟间隔的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!