CODEWITHSUNDEEP
php
Sachin Basendra
Sachin Basendra

Reputation: 125

How to count data group by per 6 hours interval in every day in timestamp from database in PHP?

this is my query:

$sql = "SELECT DATE( pm_timestamp ) AS d, HOUR( pm_timestamp ) AS h, 
       COUNT( * ) AS c FROM pm_impr_tracker WHERE campaign_id = '$camp_id' GROUP BY h,d order by `pm_timestamp`";

this gives per hours data but i want per 6 hours. my results:

    d       h   c
2015-08-23  6   6
2015-08-23  7   11
2015-08-23  8   26 
2015-08-23  9   57
2015-08-23  10  36
2015-08-23  11  19
2015-08-23  12  21
2015-08-23  13  18
2015-08-23  14  18
2015-08-23  15  17
2015-08-23  16  107
2015-08-23  17  55
2015-08-24  8   26
2015-08-24  9   57
2015-08-24  10  36
2015-08-24  11  19
2015-08-24  12  21
2015-08-24  13  18
2015-08-24  14  18

Upvotes: 2

Views: 909

Answers (1)

axiac
axiac

Reputation: 72276

The code you posted groups the records by hour. If you need them grouped by intervals of 6 hours then all you have to do is to make h be the number of such a 6 hours group in the day. For example, h = 0 is the group of hours 0..5 (i.e. HOUR(pm_timestamp) between 0 and 5), h = 1 is for hours 6..11 and so on.

This can be easily achieved if you divide the hour by 6 and ignore the remainder. That's what the MySQL DIV operator does for a living.

The query is:

SELECT DATE(pm_timestamp) AS d,
    HOUR(pm_timestamp) DIV 6 AS h, 
    COUNT(*) AS c
FROM pm_impr_tracker
WHERE campaign_id = '$camp_id'
GROUP BY h, d

Upvotes: 1

Related Questions