MySQL:从日期时间字段获取 "busiest"或 "most popular"小时？

Question

考虑下表，其中包含字段 - id (int) 和 date_created (datetime):

id       date_created

 1       2010-02-25 12:25:32
 2       2010-02-26 13:40:37
 3       2010-03-01 12:02:22
 4       2010-03-01 12:10:23
 5       2010-03-02 10:10:09
 6       2010-03-03 12:45:03

我想知道这组数据一天中最忙/最流行的时间。在此示例中，我要查找的结果是 12。

想法？

Answer 1

要仅获取最流行的时段，请使用此查询

select date_format( date_created, '%H' ) as `hour`
  from [Table]
 group by date_format( date_created, '%H' )
 order by count(*) desc
 limit 1;

如果你想查看所有数据，就用这个

select count(*) as num_records
     , date_created
     , date_format( date_created, '%H' ) as `hour`
  from [Table]
 group by `hour`
 order by num_records desc;