Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Select and group rows in summary by hour

Tags:

I have a table containing view/click records. The time is stored in a unix timestamp and I need to be able to pull out all of them within the specific month/day (based off of timestamps), but more importantly and the part I don't know how to do is group them by hour. I need to be able to do this in a single query rather than looping through each hour.

Database is MySQL, language is PHP.

like image 884
Webnet Avatar asked Oct 27 '09 19:10

Webnet


People also ask

How do you group multiple rows in Excel?

On the Data tab, in the Outline group, click Group. Then in the Group dialog box, click Rows, and then click OK. Tip: If you select entire rows instead of just the cells, Excel automatically groups by row - the Group dialog box doesn't even open. The outline symbols appear beside the group on the screen.

How do I group transactions in Excel?

Select the rows you wish to add grouping to (entire rows, not just individual cells) Go to the Data Ribbon. Select Group. Select Group again.


1 Answers

select hour(somedate), count(*) from yourtable group by hour(somedate)

If you need all three:

select month(somedate), day(somedate), hour(somedate), count(*) from yourtable group by month(somedate), day(somedate), hour(somedate)
like image 171
ʞɔıu Avatar answered Nov 28 '22 14:11

ʞɔıu