Select date and time groupings in MySQL, grouped by hour

Question

I have a date data date in a mysql table as follows:

2010-02-13 1:00:00, "soma data" 2010-02-13 1:25:00, "soma data" 2010-02-13 1:37:00, "soma data" 2010-02-13 2:12:00, "soma data" 

I want to select a report that displays data grouped by hour, for example:

 On Feb 13, during the hour from 1:00 pm to 1:59 pm, there were 3 data points. On Feb 13, during the hour from 2:00 pm to 2:59 pm, there was 1 data points. ... 

In principle, I want to report on the total number of records that occurred during each hour of the day. Thus, the end result would give me a 10-day report that erupted in a 24-hour increment, so I can see how much data is available for any given hour on any day.

TIA, I hope you can help!

Answer 1

You can use the GROUP BY as in the following query:

 SELECT COUNT(*), YEAR(dateTimeField), MONTH(dateTimeField), DAY(dateTimeField), HOUR(dateTimeField) FROM yourTable WHERE dateTimeField >= '2010-02-04 00:00:00' AND dateTimeField < '2010-02-14 00:00:00' GROUP BY YEAR(dateTimeField), MONTH(dateTimeField), DAY(dateTimeField), HOUR(dateTimeField); 

You can also check the MySQL documentation for further reference on date and time functions:

• MySQL 5.1 Reference Manual - Date and time functions

Answer 2

 SELECT HOUR(datetime), COUNT(*) FROM table GROUP BY HOUR(datetime); 

During this hour you will receive two fields: hour and number of events.