效果图
最终sql
-- 最终sql
select ceil((hour(transaction_time)/2))*2 统计时间
,date_format(
concat(date(transaction_time),' ', ceil((hour(transaction_time)/2))*2),'%Y-%m-%d %H:00:00')统计时间格式
from sm_order_item
WHERE date(transaction_time)= '2020-05-22'
group by 统计时间
order by 统计时间
思考逻辑
要每两小时统计一次,0-2,2-4……
-- 每两小时一统计 六点(5-6) 八点(7-8)……
select hour(transaction_time) 原小时, ceil((hour(transaction_time)/2)) 原小时向上取整,ceil((hour(transaction_time)/2))*2 统计时间 from sm_order_item WHERE date(transaction_time) = '2020-05-23'
group by hour(transaction_time)
order by hour(transaction_time)