mysql 判断五分钟之内,MySQL：选择上一个完整的5分钟间隔

I want to select from my table the last full 5 minute interval.

Example:

Time now: 09:32 am --> select --> 09:25 - 09:30 am

Time now: 10:44 pm --> select --> 10:35 - 10:40 pm

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,

I also know how to select the last 5 minutes

where (endtime between now()-Interval 5 minute and now())

But how do I get the last full 5 minute interval like shown in the example above?

解决方案

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();

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.