CODEWITHSUNDEEP
mysql
Starlin
Starlin

Reputation: 845

mysql number of rows per hour

I have a table where each row has a timestamp. Is there a query I can run that can count the number of rows per hour. Outputting a row for each hour with a field for the number of rows encompassed?

e.g 

id timestamp

10 2010-09-19 21:05:05

11 2010-09-19 22:05:30

12 2010-09-19 23:05:05

13 2010-09-19 23:05:05

number of rows | hour
1                21
1                22
2                23

I guess there might be problems with using the hour as there will be duplicate hours... so maybe '2010-09-19 21' or just a number representing the period (e.g in the example 2010-09-19 21 is 1)

TIA

Upvotes: 14

Views: 22063

Answers (3)

6opko
6opko

Reputation: 1760

Based on D'Arcy Rittich's answer, this worked for me - for months and years:

SELECT YEAR(timestamp), MONTH(timestamp), DAY(timestamp), HOUR(timestamp), COUNT(*) FROM MyTable WHERE timestamp BETWEEN :date1 AND :date2 GROUP BY YEAR(timestamp), MONTH(timestamp), DAY(timestamp), HOUR(timestamp);

Upvotes: 4

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171351

It depends on how big your date range is. If all dates fall within a month, e.g., you could do this:

select day(timestamp) as Day, hour(timestamp) as Hour, count(*) as Count
from MyTable
where timestamp between :date1 and :date2
group by day(timestamp), hour(timestamp)

You can group by year and month as well if you need to further separate your data.

Upvotes: 31

Pedro Gil
Pedro Gil

Reputation: 541

SELECT HOUR(hour) as hour, COUNT(*) as num_rows FROM `TABLE` GROUP BY HOUR(hour)

Upvotes: 6

Related Questions