MySQL:选择上一个完整的5分钟间隔 [英] MySQL: select last full 5 minute interval

问题描述

我想从表中选择最近的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分钟间隔的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！