MySQL: select last full 5 minute interval

问题:

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?

回答1:

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.

回答2:

If you just want to limit the result set to records happening within 5 minutes of the most recent endtime then you can try the following:

SELECT *
FROM yourTable
WHERE endtime > (SELECT MAX(endtime) FROM yourTable) - INTERVAL 5 MINUTE

回答3:

You were close:

WHERE endtime > date_sub(now(), interval 3 minute);

回答4:

use

select ceil(minute(now())/5) as `x`

You will get x (1-12), after that just multiply it by 5

if we are are "32" then x = 7 (32/5 = 6.x => 7)

between ((x-1)*5) and (x*5)    = (7-1)*5 and 7*5 = 30-35

=== added ====

just concat it into an hour

回答5:

I think I got it:

select  
   a.end_time - Interval 5 minute as start_time,
   a.end_time
from
   (
    select 
      str_to_date(concat(date(now()), ' ', hour(now()),':',floor(minute(now())/5)*5),'%Y-%m-%d %H:%i') as end_time
  ) a

Result