How can I create a statistics tool with PHP/MySQL from existing Timestamps?

I have an existing MySQL table with various fields.

userid | name | ... | timestamp

I'd like to have a statistics tool which shows me how many entrys were made in a specific day and in which hour of that day.

---

Example:

Statistics for: 02-24-2011

Total entrys: XXXXX

Hour 1: XX //Which should be 12 AM to 开发者_如何学运维1 AM

Hour 2: XX

Hour 3: XX ...

---

How can this be done? Thanks for all your help!

DC

---

SELECT HOUR( timestamp ) AS the_hour, COUNT(*) AS entries 
FROM your_table
WHERE DATE( timestamp ) = NOW()
GROUP BY HOUR( timestamp )

it will return counters hour by hour from today (change NOW() if need to change day), if no entry in specific hour -> no record,

---

Try that

SELECT DATE_FORMAT(timestamp, '%m-%d-%Y'), COUNT(userid) FROM your_table_name
WHERE DATE_FORMAT(timestamp, '%Y-%m-%d %H:%i:%s') >= '2011-02-24 00:00:00' AND DATE_FORMAT(timestamp, '%Y-%m-%d %H:%i:%s') <= '2011-02-24 23:59:59'
GROUP BY DATE_FORMAT(timestamp, '%Y-%m-%d %H')