mysql - 在日期时间和组中选择小时

mysql - select hour in datetime and group

提问by Jon Skeet

I have a datetime column of ShoppingDates.

我有一个 ShoppingDates 的日期时间列。

Lets say I have 1000 rows of

假设我有 1000 行

7/18/2012 5:33:39 PM

2012/7/18 下午 5:33:39

7/16/2012 6:64:39 PM

2012/7/16 下午 6:64:39

7/14/2012 7:34:39 PM

2012/7/14 下午 7:34:39

7/13/2012 8:30:39 PM

2012/7/13 晚上 8:30:39

7/13/2012 8:37:39 PM

2012/7/13 晚上 8:37:39

等等....

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!

谢谢！

回答by Jon Skeet

I suspect you just want something like:

我怀疑你只是想要这样的东西：

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

回答by Akash

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 是一个日期时间字段