mysql - how to count number of records in an hour (datetime) using sql

Question

Possible Duplicate:
Group records by time

I have a mysql table that logs web page requests.

I'd like to write a sql that outputs the number of requets per hour.

INPUT:
timestamp              browser
-------------------    -------
2012-06-28 15:06:14    chrome
2012-06-28 15:12:15    IE6
2012-06-28 16:32:16    IE7

OUTPUT:
timestamp              count
-------------------    -------
2012-06-28 15:00:00    2
2012-06-28 16:00:00    1

I'm guessing my first step would be to 'truncate' the datetime field so that I can do a group-by. Searching, I have found some examples of rounding the hour, but none to truncate.

See: http://stackoverflow.com/questions/10110341/group-records-by-time — David Manheim, Jun 28 '12 at 13:54
That question is not an exact duplicate, it groups by 15 minute intervals. In addtion, the answers here are also not duplicates, as they use the date_format functions which are not used in answers to the other question... To merge these, both the questions and answers would need to be modified. — Eric, Aug 09 '12 at 12:48

Muhammad Raheel · Answer 1 · 2012-06-28T14:53:22.583

5

select count(no_of_requests) from table group by date(datetime_field) , hour(datetime_field)

edited Jun 28 '12 at 14:53

answered Jun 28 '12 at 13:55

Muhammad Raheel

19,823
7
67
103

score 4 · Answer 2 · answered Jun 28 '12 at 13:57

4

Try formatting the date with

DATE_FORMAT(timestamp, '%Y-%m-%d %H') as formatted_timestamp

and then grouping on that formatted_timestamp

answered Jun 28 '12 at 13:57

Ekster

353
1
4

+1 For considering the date too. – Ravinder Reddy Jun 28 '12 at 14:30
He could use the other suggestion as well but then he'd have to group using both functions; group by date(datetimefield), hour(datetimefield) – Ekster Jun 28 '12 at 17:34

score 3 · Answer 3 · answered Jun 28 '12 at 14:49

Assuming ts_table as your table name and ts as your timestamp column name,

mysql> select
    ->     addtime( date_format( ts, '%Y-%m-%d %H' ), '00:00:00' ) as 'during'
    ->     , count( date_format( ts, '%Y-%m-%d %H' ) ) as 'visit_count'
    -> from ts_table
    -> group by 'during'
    -> ;
+---------------------+-------------+
| during              | visit_count |
+---------------------+-------------+
| 2012-06-28 15:00:00 |           2 |
| 2012-06-28 16:00:00 |           1 |
+---------------------+-------------+
2 rows in set (0.00 sec)

score 1 · Answer 4 · answered Jun 28 '12 at 13:56

1

You should be able to group by DATE() and HOUR() functions to get your output.

answered Jun 28 '12 at 13:56

pedromarce

5,651
2
27
27

mysql - how to count number of records in an hour (datetime) using sql

4 Answers4