mysql时间偏移,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:

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.

Thanks !!

解决方案

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

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