CODEWITHSUNDEEP
phpmysqlsqldatetimeaggregate-functions
NewPHP
NewPHP

Reputation: 608

MYSQL query for every hour

$today = mktime(0,0,0);
$yesterday = $today - 86400;
$this->view->numberyesterday=$db->fetchAll("SELECT * FROM `transactions` WHERE `siteid`=".$sid." AND `timestamp` > ".$db->quote($yesterday,'integer')." AND timestamp < ".$db->quote($today,'integer'));

I have used the above code to display all the records of yesterday. Now I need to plot the graph for each hour. How can I retrieve the records in every hour from the above query?( like, records within 12-1 AM, 1-2 AM....23-24PM etc)

Thanks!

Edit:

I wish I could store the records of each hour in each variable. So that there will be 24 variable for 24 hours. So it is easy to draw the graph.

Upvotes: 0

Views: 2362

Answers (1)

Adriano Carneiro
Adriano Carneiro

Reputation: 58615

You did not specify how the output would look like, but it seems like this construct would help you:

select Hour(timestamp) transhour, count(*) transcount
from transactions
where yourfilters = yourvalues
Group by Hour(timestamp)

The output will look like this:

transhour | transcount
======================
0         | 2
1         | 23
2         | 45
3         | 23
...       | ...
22        | 34
23        | 3

Warning: if no transactions were performed in a given hour, the output will omit this hour (and not bring it back with count zero)

Upvotes: 5

Related Questions