MySQL - 如何将小时分组，偏移30分钟 [英] MySQL - how to group hour, offset by 30 minutes

问题描述

我想以1小时为间隔选择记录，以及小时间隔中的最高值和最低值。我可以在一小时内做到这一点（例如，凌晨1点，凌晨2点，凌晨3点），但我想按照指定的分钟时间（例如上午1点30分，凌晨2点30分，凌晨3点30分，凌晨1点50分，凌晨2点50分，凌晨3点50分）。我不想按半小时进行分组（例如1：00,1：30,2：00,2：30）
这是我每隔1小时执行一次的SQL：

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

我试过了：hour（日期）+ .5'aHour'：但它不起作用。

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

感谢!!

推荐答案

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

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

FromHour和ToHour只是字符串

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

这篇关于MySQL - 如何将小时分组，偏移30分钟的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！