CODEWITHSUNDEEP
mysqldatetimeintervals
LStrike
LStrike

Reputation: 1648

MySQL: select last full 5 minute interval

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

Example:

enter image description here

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?

Upvotes: 2

Views: 5822

Answers (5)

LStrike
LStrike

Reputation: 1648

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

enter image description here

Upvotes: 0

Utsav
Utsav

Reputation: 8093

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.

Upvotes: 1

gonatee
gonatee

Reputation: 101

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

Upvotes: 0

Matt
Matt

Reputation: 15071

You were close:

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

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521289

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

Upvotes: 1

Related Questions