Select hits grouped by day including days with 0 hits

Question

I have a table which logs hits on urls

The goal is to get all hits for parent grouped by days

parent representing the id of the url

time being an int generated by PHP's time()

id | parent | time

The query I came up with so far: (assuming I would want to select item 2929)

SELECT time, count( * ) AS hits
FROM `hits`
WHERE parent =2929
GROUP BY floor( time /86400 )
ORDER BY time ASC

The problem now is, that I only get 5 rows

time            hits
1355761244      9
1355791721      22
1355879697      23
1355966014      16
1356070906      24

Since MySQL won't return days with 0 hits.

How can I fix this?

Goal would be to e.g. select the last 7 or 30 or 365 days and receiving days with 0 hits aswell

Updated Query to select days of the last month:

SELECT floor(time/86400) AS day, count( * ) AS hits
FROM `hits`
WHERE parent =2929
AND time > ONE_MONTH_AGO
GROUP BY day
ORDER BY time ASC
LIMIT 0 , 30

With Pharaoh's PHP function this should work fine.

[Get total views per day including leaving “0 views” for a day that has no record](http://stackoverflow.com/questions/12936133/get-total-views-per-day-including-leaving-0-views-for-a-day-that-has-no-record) seems related. — DCoder, Dec 22 '12 at 13:52

Imanuel · Accepted Answer · 2012-12-22T13:59:11.770

2

Since there are no hits on a day with no hits there is no database entry for this day - so MySQL can't select anything.
I would do this in PHP after the query is done ("fill holes").

Either you do a data postprocessing like that:

for ($day=$minday; $day<=$maxday; $day++)
  if (!isset($sql_array[$day]))
    $sql_array[$day]=0;

Or you do it directly while echoing the data:

for ($day=$minday; $day<=$maxday; $day++)
  if (!isset($sql_array[$day]))
    echo 0;
  else
    echo $sql_array[$day];

I assumed $day to be an integer counting the days, you'll have to modify it to fit your needs.

edited Dec 22 '12 at 13:59

answered Dec 22 '12 at 13:52

Imanuel

3,596
4
24
46

Could you implement my updated query so we have a nice answer? – Wurstbro Dec 22 '12 at 14:02

score 1 · Answer 2 · answered Dec 22 '12 at 14:02

Set your start and end dates in the form of timestamps and use the PHP range() function to create an array of those timestamps. Set the values in the array to zero, then map the query results set into the array. Something like this to get the array of zeros.

<?php // RAY_temp_foo.php
error_reporting(E_ALL);
date_default_timezone_set('America/Chicago');

$old = strtotime('September 15, 2012');
$now = strtotime('October 15, 2012');
$day = 60*60*24;
$arr = range($old, $now, $day);
$new = array_flip($arr);
foreach ($new as $key => $nul) $new[$key] = 0;
var_dump($new);

You may need to normalize your 'time' values to line up with the keys in the array, so that they represent the starting second of each day.

Select hits grouped by day including days with 0 hits

2 Answers2