在datetime和group中选择hour

I have a datetime column of ShoppingDates.

我有一个datetime的购物日期专栏。

Lets say I have 1000 rows of

假设有1000行

7/18/2012 5:33:39 PM

7/18/2012 5:33:39点

7/16/2012 6:64:39 PM

7/16/2012 6:64:39点

7/14/2012 7:34:39 PM

7/14/2012 7:34:39点

7/13/2012 8:30:39 PM

7/13/2012 8:30:39点

7/13/2012 8:37:39 PM

7/13/2012 8:37:39点

ect....

I want to count the number of times that each time appears within an hour time frame

我想计算每次出现在一个小时内的次数

so 5:00 p.m - 5:59:59 pm
so 6:00 p.m - 6:59:59 pm
so 7:00 p.m - 7:59:59 pm
so 8:00 p.m - 8:59:59 pm

so in the above result I would get

在上面的结果中

1|5pm-6pm
1|6pm-7pm
1|7pm-8pm
2|8pm-9pm

Any help is appreciated.

任何帮助都是感激。

Thanks!

谢谢!

2 个解决方案

#1

I suspect you just want something like:

我猜你只是想要:

SELECT HOUR(ShoppingDate), COUNT(*) FROM YourTable
GROUP BY HOUR(ShoppingDate)

#2

Try this

试试这个

SELECT 
  CONCAT( HOUR(ShoppingDate), ' to ', CONCAT( HOUR(ShoppingDate), ':59:59' ) ) as time_frame,
  COUNT(*) 
FROM 
  temp
GROUP BY 
  DATE(ShoppingDate), 
  HOUR(ShoppingDate)

Here's the SQLfiddle

这是SQLfiddle

Note: I assume ShoppingDate is a datetime field

注意:我假设ShoppingDate是一个datetime字段

#1