MySQL -如何分组小时，以30分钟为间隔

I'd like to select records in 1 hour intervals, along with the highest and loest value in the hour interval. I can do this on the hour (eg, 1:00 am, 2:00 am, 3:00 am), but I'd like to offset it by specified minutes (eg, 1:30 am, 2:30 am, 3:30 am, or 1:50am, 2:50 am, 3:50 am). I don't want to group by half-hour (eg 1:00, 1:30, 2:00, 2:30) This is the SQL I have for 1 hour intervals:

我想在1小时间隔内选择记录，并在1小时间隔内选择最高和最低的值。我可以在1点(比如，凌晨1点，凌晨2点，凌晨3点)做这个，但是我想用指定的分钟来抵消(比如，1点30分，2点30分，3点30分，或者1点50分，2点50分，3点50分)。我不想按半小时(如1:00,1:30,2:00,2:30)进行分组，这是我每隔一小时的SQL语句:

select date(date) 'aDate', hour(date) 'aHour', date, bidOpen, max(bidHigh), min(bidLow)
from data
where date > "2010-10-10"
group by aDate, aHour

I tried: hour(date) + .5 'aHour' : but it didn't work.

我试过:小时(日期)+ .5 '小时':但它不起作用。

Thanks !!

谢谢! !

2 个解决方案

#1

A solution is to use an helper table which contains your timeslice.

解决方案是使用包含时间片的帮助表。

The FromHour and ToHour are just strings

从时到时只是字符串

TABLE timeslice
ID | FromHour | ToHour | NextDay
---+----------+--------+-------
1  | 00:30    | 01:30  | 0
2  | 01:30    | 02:30  | 0
<snip>
24 | 23:30    | 00:30  | 1

select date(date) aDate, ID ,date, bidOpen, max(bidHigh),min(bidLow)
from data inner join timeslice
    ON date >= CONCAT(date(Date),' ',FromHour) 
    and date < concat(date(DATEADD(day,NextDay,date),' ',ToHour)
group by aDate, ID

#2

If you store your date/times as timestamps, you can do mathy things like GROUP BY FLOOR(MOD((mytimestamp-1800)/3600)) for hour intervals starting on the half hour.

如果您将日期/时间存储为时间戳，您可以按照半小时开始的时间间隔，按层对层(MOD(mytimestamp-1800)/3600)做大量工作。

#1